Using excels named range in a sql string in VBScript

Question

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 & "]"

If you ran those first two lines exactly as written in VBScript then it will not work, because the constant `xlUp` is not defined outside of Excel. You need to replace it with its actual value (which you can find using the object browser in excel (F2 in the VB Editor). It would help to update your question with the complete VBscript you're using. — Tim Williams, Apr 05 '13 at 16:22
Tim, I can see where you going with it - can you please explain a little bit more how could i use ( overwrite ) the Excel.Direction class, or how to declare xlUp as a Constant ( it cant as simple as declaring constant with a value of Const xlUp = -4162 (&HFFFFEFBE) ? ) — , Apr 08 '13 at 07:15
I added another suggestion in my answer below, one problem is that your code to create the named range doesn't work, and even if it did, JET can't see it unless the workbook is saved, that's why you need a **Schema File** (Schema.ini) — Our Man in Bananas, Apr 08 '13 at 08:42
Yes - you just declare a constant, or use the value directly in your code. — Tim Williams, Apr 08 '13 at 16:18

Our Man in Bananas · Answer 1 · 2013-04-08T08:40:59.740

3

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.

edited Apr 08 '13 at 08:40

answered Apr 05 '13 at 16:02

Our Man in Bananas

5,809
21
91
148

Philip, Thanks for the links. I have read the articles, however, unfortunately, I have not found it very useful. Most of it its something I have already knew, or something that does not really apply to my problem. I cannot use MS Query because lets say the file im opening is going to change every day and the structure of the file isnt any close to a RDB like design. I have to iterate through the file and find the ranges. I will update my original post with examples. Thanks for your answer however, I cannot mark it as a good answer, sorry – Apr 08 '13 at 07:11
What I mean is, use MS Query inside MS Excel (from the Data Menu)to run the Query **SELECT * FROM myRange** *once* to ensure the range is identified and found by the Jet DB Engine – Our Man in Bananas Apr 08 '13 at 08:21
1

Its still throwing Expected Statement error. I have found a solution. Let me post. sometimes the simplest approach will do the job... screw named ranges :P – Apr 08 '13 at 09:23
@mehow - EXACTLY:) Screw those darned named ranges... use the worksheet name with *`* and *!* – Our Man in Bananas Apr 08 '13 at 09:52

score 1 · Accepted Answer · 2013-07-08T12:57:53.263

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

Using excels named range in a sql string in VBScript

2 Answers2

Linked