What's the table name for a defined name in the scope of a worksheet?

Question

I'm trying to query a defined name in a worksheet scope with SQL using ODBC.

I already know that this works:

SELECT * FROM [worksheet1$]       -- Query a whole worksheet
SELECT * FROM [worksheet1$A1:C10] -- Query A1:C10 of a worksheet
SELECT * FROM myname              -- Query a workbook scoped defined name 

Note that myname is workbook scoped and not worksheet scoped.

I would have expected that something like in Excel formulas works for the ODBC table name as well:

SELECT * FROM worksheet1!myname   -- Query a worksheet scoped defined name

However this doesn't work, I tried some syntax variations of this.

Is this possible and if it is, what's the syntax to use?

Note for Delphi developers: The A1:C10 syntax causes trouble in TADOQuery, you have to set ParamCheck to False in order for this to work.

Update:

I'm using the Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) Version 12.00.6606.1000 that comes with Microsoft Office 2007.

I have checked that the Delphi database components I'm using are behaving the same way as Microsoft Query and Microsoft Access 2007.

Answer 1

Try the following to help you figure out the name Delphi is looking for.

1. Drop a combo box on a form.
2. Run the following code to populate the combobox with the names Delphi will accept: AdoConnection1.GetTableNames(ComboBox1.Items,True);
3. Run the app. The combobox should give you a list of queryable names.

Using Delphi XE2 and Office 2010 (excel file saved as office 97 xls format) I created a worksheet scoped named range (sheet1!numberlist) and ran an ADO query in Delphi. The query was SELECT * FROM [sheet1$numberlist]. Maybe it's your ODBC driver?