Excel 2003 files in XL2007 querying named range

[ebrooks54]

I have an XL2003 spreadsheet that a user has created an MSQuery in to a named range in another XL2003 spreadsheet.

In XL2007 it appears this only works if the spreadsheet being queried is opened first. If it is not open, the spreadsheet doing the querying gets an error message saying it can not find the named range. If the spreadsheet is open, the queries refresh with no problem.

Has anyone else noticed this behaviour? Does anyone know why this has changed?

I have tried playing around with the SQL and the Connection String that was created, but short of recreating the querying spreadsheet from scratch, I can't seem to make the existing spreadsheet work. There are over a dozen queries in this spreadsheet and I would prefer not to have to recreate unless I have to.

Comments and thoughts on what is going on here would be appreciated.

 

[SkipVought]

hi,

Use MS Query lots, in 2007.

If your named range is a CALCULATED range, is using a function in Define Names, then I woudl suspect that the Named Range woudl not resolve.

Please describe the sheet where this named range resides.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ebrooks54]

Thanks for replying.

The range does not appear to be calculated. According to Name Manager it is defined as =OTIS!$A:$BX. OTIS is the name of the worksheet.

In playing with MSQry32, I have noticed that if the spreadsheet is not open, the available tables it discovers are OTIS$ and OTIS$FilterDatabase. If the spreadsheet is open in Excel the available tables are DATA and OTIS$. DATA is the name of the range.

 

[SkipVought]

In the MS Query Add Tables window, hit the OPTIONS button, and CHECK all selection.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ebrooks54]

Tables, Views, System Tables, and Synonyms are all Checked.

If the file isn't open, the Named Range is not appearing.

 

[SkipVought]

Hmmmmm???

Works for me!

The way your DATA Named Range is defined, According to Name Manager it is defined as =OTIS!$A:$BX, you ought to be able to query OTIS$ instead.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]