Workbook named range not seen by ADO when workbook closed

[Tim Lund]

Running VBA in a separate workbook:

r is an ADODB recordset,
cnn a connection to an Excel workbook which includes
a sheet called IDB,
a workbook range name also called LEVEL_1, and
a named range whose name COUNTRY is a member of sheets("IDB").names.

The Excel workbook is not open.

Of the following alternative lines

r.Open "SELECT * FROM [IDB$]", cnn
r.Open "SELECT * FROM [IDB$A1:C50]", cnn
r.Open "SELECT * FROM [LEVEL_1]", cnn
r.Open "SELECT * FROM [IDB$COUNTRY]", cnn

the thiird produces an error - "Jet engine not able to find object
LEVEL_1.". But only when the workbook is closed.

Why?

 

[paul.robinson]

Hi
Just looking at "Excel 2002 VBA" they have an example:
Worksheet name:
"SELECT * FROM [Sales$]"
Sheet level range name:
"SELECT * FROM [Sales$SheetLevelName]"
Specific Range Address:
"SELECT * FROM [Sales$A1:E89]"
Book level Range Name:
"SELECT * FROM BookLevelName

So maybe you want

r.Open "SELECT * FROM LEVEL_1", cnn

regards
Paul

 

[Tim Lund]

Sorry - I should have made clear that I'd tried it without the brackets as
well. So the mystery remains.

Hi
Just looking at "Excel 2002 VBA" they have an example:
Worksheet name:
"SELECT * FROM [Sales$]"
Sheet level range name:
"SELECT * FROM [Sales$SheetLevelName]"
Specific Range Address:
"SELECT * FROM [Sales$A1:E89]"
Book level Range Name:
"SELECT * FROM BookLevelName

So maybe you want

r.Open "SELECT * FROM LEVEL_1", cnn

regards
Paul

Running VBA in a separate workbook:

r is an ADODB recordset,
cnn a connection to an Excel workbook which includes
a sheet called IDB,
a workbook range name also called LEVEL_1, and
a named range whose name COUNTRY is a member of sheets("IDB").names.

The Excel workbook is not open.

Of the following alternative lines

r.Open "SELECT * FROM [IDB$]", cnn
r.Open "SELECT * FROM [IDB$A1:C50]", cnn
r.Open "SELECT * FROM [LEVEL_1]", cnn
r.Open "SELECT * FROM [IDB$COUNTRY]", cnn

the thiird produces an error - "Jet engine not able to find object
LEVEL_1.". But only when the workbook is closed.

Why?