Using excels named range in a sql string in VBScri

问题:

I have searched on here and google but I still cannot solve my issue. I am trying to use an excel's named range equivalently in my .vbs file. The below works in VBA in excel but I cannot get it to work in a *.vbs file.

ThisWorkbook.Sheets(1).Range("A1:B" & Range("B" & Rows.Count).End(xlUp).Row).Name = "DATA"
strSql = "SELECT * FROM DATA"

So, I have tried different variations of referencing to my named range DATA with no luck.
This is what I have now:

Set rng = ws.Range("A1:B2")
rng = "DATA"    
strSql = "SELECT * FROM DATA"

Some different variations involved: taking parameter ByVal, using rng instead of DATA (string type), SELECT * FROM " & rng, etc..

The error msg when running:

Microsoft (R) Windows Script Host Version 5.8 Copyright (C) Microsoft Corporation. All rights reserved.

C:\Users\admin\Desktop\UpdateSourceTbl.vbs(119, 5) Microsoft JET Databas e Engine: The Microsoft Jet database engine could not find the object 'DATA'. M ake sure the object exists and that you spell its name and the path name correctly.

Any help greatly appreciated!

TEMPORARY SOLUTION: I used 2 parameters with row numbers, may not be the best solution - but it works! and i cant see nothing wrong with it to be fair

Call createAndInsertRecordSet(wb.FullName, ws.Name, i+1, j-1)
Sub CreateAndInsertRecordSet(ByVal fullname, ByVal wsName, ByVal stRow, byVal enRow )
strSql = "SELECT * FROM [" & wsName & "$B" & stRow & ":AX" & enRow & "]"

回答1:

EDIT: Please change the line

ws.Range("B2:AX2") = "MyRange"

with

activeworkbook.Names.Add Name:="myRange", RefersTo:="B2:AX2"

I think that will properly create the name DATA.

Unfortunately it still may not work without SAVING THE WORKBOOK as JET OLE DB Provider/DB Engine works on a file on Disk, not in memory

Here I think you may need to dynamically create a Schema.ini file to define the columns you want.

Remember that JET expects to see data in columns, so if there are columns to be skipped, then perhaps they need to be defined in a Schema file even if it means you have to dynamically write the schema at runtime

Another point here, the error you are geeting can be checked/debugged by running the query using MS Query in MS Excel to see if the JET DB Engine can see the DATA range

you need to read up on how to access Excel data use ADO/OLE DB

first, to find out how to reference your DATA named range, open MS Query in Excel and query the sheet

see this site: Use MS Query to Treat Excel As a Relational Data Source

see the below links:

• MSDN KB: How To Use ADO with Excel Data from Visual Basic or VBA
• Treat Excel As a Relational Data Source on the Excel User MVP website
• Office Space: Using ADO to Query an Excel Spreadsheet

remember, what works in Excel VBA inside Excel's VB Editor will not work the same way in VBScript as there is no Type declaration, and no Intellisense.

回答2:

Work can be unpredictable and sometimes we have to go back and modify, fix, reuse code from a while ago. It happened today. I have gone back and reproduced my code and got it working the first time. I must have been doing something wrong when I posted this question, looking at the wrong lines or procedures or something strange. The below code runs perfectly.
it:
- opens a workbook
- establishes a connection with the workbook to retrieve data in a recordset
- opens up a connection to a database and executes a sample insert statment
After running the code I have checked the Temporary databases, the values have been inserted, so I can confirm this is my working solution to the problem originally raised.

Option Explicit

Private Const adUseClient = 3
Dim xl, wb, ws, fPath, rng

fPath = "C:\Users\admin\Desktop\Book1.xlsm"

Call OpenFile()
Call InsertRecordset()
Call CloseFile()

Private Sub OpenFile()
    Set xl = CreateObject("Excel.Application")
    xl.Visible = False
    Set wb = xl.Workbooks.Open(fPath)
    Set ws = wb.Sheets(1)
End Sub

Private Sub CloseFile()
    wb.Saved = True
    wb.Close
    xl.Quit
    Set wb = Nothing
    Set xl = Nothing
End Sub

Private Sub InsertRecordset()

    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & wb.fullname & ";Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"";"
    Dim cn, rs, strCon, strSql, cn2

    ws.Range("A1:B2").Name = "DATA"
    strSql = "SELECT * FROM DATA"

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    cn.Open strCon          
    rs.Open strSql, cn      

    Set cn2 = CreateObject("ADODB.Connection")
    With cn2
        .CursorLocation = adUseClient
        .Open "Driver={SQL Server};Server=HELIUM\PRI; Database=TEMPORARY; UID=admin; PWD=password"
        .CommandTimeout = 0
        rs.MoveFirst
        Do While Not rs.EOF
            .Execute "INSERT INTO TEMPORARY.dbo.TEMP_TABLE ( [TEMP_COLUMN] ) VALUES ('" & rs.Fields(1) & "')"
            rs.MoveNext
            Loop
    End With

    ' Close connections
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    cn2.Close
    Set cn2 = Nothing
End Sub