vba - Using excels named range in a sql string in VBScript -

i have searched on here , google still cannot solve issue. trying use excel's named range equivalently in .vbs file. below works in vba in excel cannot work in *.vbs file.

thisworkbook.sheets(1).range("a1:b" & range("b" & rows.count).end(xlup).row).name = "data" strsql = "select * data" 

so, have tried different variations of referencing named range data no luck.
have now:

set rng = ws.range("a1:b2") rng = "data"     strsql = "select * data" 

some different variations involved: taking parameter byval, using rng instead of data (string type), select * " & rng, etc..

the error msg when running:

microsoft (r) windows script host version 5.8 copyright (c) microsoft corporation. rights reserved.

c:\users\admin\desktop\updatesourcetbl.vbs(119, 5) microsoft jet databas e engine: microsoft jet database engine not find object 'data'. m ake sure object exists , spell name , path name correctly.

any appreciated!

temporary solution: used 2 parameters row numbers, may not best solution - works! , cant see nothing wrong fair

call createandinsertrecordset(wb.fullname, ws.name, i+1, j-1) sub createandinsertrecordset(byval fullname, byval wsname, byval strow, byval enrow ) strsql = "select * [" & wsname & "$b" & strow & ":ax" & enrow & "]" 

edit: please change line

ws.range("b2:ax2") = "myrange" 

with

activeworkbook.names.add name:="myrange", refersto:="b2:ax2" 

i think create name data.

unfortunately still may not work without saving workbook jet ole db provider/db engine works on file on disk, not in memory

here think may need dynamically create schema.ini file define columns want.

remember jet expects see data in columns, if there columns skipped, perhaps need defined in schema file even if means have dynamically write schema @ runtime

another point here, error geeting can checked/debugged running query using ms query in ms excel see if jet db engine can see data range

you need read on how access excel data use ado/ole db

first, find out how reference data named range, open ms query in excel , query sheet

see site: use ms query treat excel relational data source

see below links:

• msdn kb: how use ado excel data visual basic or vba
• treat excel relational data source on excel user mvp website
• office space: using ado query excel spreadsheet

remember, works in excel vba inside excel's vb editor not work same way in vbscript there no type declaration, , no intellisense.