Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default Workbook named range not seen by ADO when workbook closed

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?
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 789
Default Workbook named range not seen by ADO when workbook closed

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

On Mar 15, 5:04 pm, Tim Lund
wrote:
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?



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default Workbook named range not seen by ADO when workbook closed

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

" wrote:

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

On Mar 15, 5:04 pm, Tim Lund
wrote:
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?




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Named Range From One Workbook Used in Validation Drop Down in 2nd Workbook Minitman Excel Discussion (Misc queries) 3 August 19th 08 05:30 PM
INDIRECT and Named Ranges referencing closed workbook gpie Excel Worksheet Functions 9 October 6th 05 11:24 PM
ADO - closed workbook - Named range MattShoreson[_16_] Excel Programming 2 August 31st 05 10:37 AM
referencing a named range from a closed workbook Gixxer_J_97[_2_] Excel Programming 4 April 8th 05 08:45 PM


All times are GMT +1. The time now is 02:20 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"