ODBC and Excel

[Guest]

I apologize if this question was somewhere else in this group.

My application requires i use ODBC to connect to a data source, In my case
i'm using Excel. I choose the Excel DSN from the list and navigate to the
speadsheet i want to use. The next screen asks me for a table name, but the
Select a table name: screen is blank. Does this make sense? I assumed the
table names would prefill the worksheets which are included w/in the XLS
spreadsheet.

Any help would be appreciated.

 

[Guest]

I forgot to mention, i'm using Office 2003 w/SP 1.

 

[Jamie Collins]

My application requires i use ODBC to connect to a data source, In my case
i'm using Excel. I choose the Excel DSN from the list and navigate to the
speadsheet i want to use. The next screen asks me for a table name, but the
Select a table name: screen is blank. Does this make sense? I assumed the
table names would prefill the worksheets which are included w/in the XLS
spreadsheet.

Strictly speaking, an Excel TABLE is a workbook level defined Name. A
worksheet or a sheet-level defined Name is considered to be a SYSTEM
TABLE.

To show system tables in MS Query's Add Tables dialog, hit the Options
button and check system Tables. You can also add/amend the following
registry key (my machine is Office XP/Office10):

HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Query\Table
Options\systables = 1

FWIW you only see this distinction between TABLE and SYSTEM TABLE with
the odbc drivers; the OLE DB provider does not distinguish them due to
a bug. See http://support.microsoft.com/default.aspx?scid=kb;en-us;300948.

Jamie.

--