Excel tables vs. system tables
 

Hi:

Yesterday I had the frustration of trying to figure out why there were
no tables listed by MSQuery/Microsoft Query when I tried to import
external data from one Excel workbook to another Excel workbook. I
finally came across the answer that I had to have "Sytem Tables"
checked in the "Add Tables" dialog box in order to access the
worksheets in my source workbook.

This left the question, what are "system tables" and how (or why) are
they different than "tables"? Some extensive poking around today led
me to the below Microsoft KB article.

It seems that there is simply a semantic distinction here between
worksheets in a workbook and named ranges in a workbook. Evidently,
the worksheets themselves (as objects) are "system tables", whereas
named ranges are "tables". Is that about right?

Since there were no named ranges in my source workbook, there were no
"tables" presented in the Add Tables dialog box. It was only when I
checked "System tables" in Options was I presented with the workbook
tabs/worksheets. But even there some of the worksheets were quoted,
while a few were not. All had trailing '$' appended to the name,
while some had a '_' after the quote as well. e.g., 'dbAll$' vs.
'dbAll$'_.

Is there any source that explains the meaning of all this in one
place?

-- Roy Zider

http://support.microsoft.com/kb/257819/en-us
How To Use ADO with Excel Data from Visual Basic or VBA
Query Table Information
Of the various objects available in a relational database (tables,
views, stored procedures, and so forth), an Excel data source exposes
only table equivalents, consisting of the worksheets and the named
ranges defined in the specified workbook. Named ranges are treated as
"Tables" and worksheets are treated as "System Tables," and there is
not much useful table information you can retrieve beyond this
"table_type" property

Excel tables vs. system tables
 

Here's an article on a similar issue.


On Jan 19, 5:22*pm, FUBARinSFO wrote:
Hi:

Yesterday I had the frustration of trying to figure out why there were
no tables listed by MSQuery/Microsoft Query when I tried to import
external data from one Excel workbook to another Excel workbook. *I
finally came across the answer that I had to have "Sytem Tables"
checked in the "Add Tables" dialog box in order to access the
worksheets in my source workbook.

This left the question, what are "system tables" and how (or why) are
they different than "tables"? *Some extensive poking around today led
me to the below Microsoft KB article.

It seems that there is simply a semantic distinction here between
worksheets in a workbook and named ranges in a workbook. *Evidently,
the worksheets themselves (as objects) are "system tables", whereas
named ranges are "tables". *Is that about right?

Since there were no named ranges in my source workbook, there were no
"tables" presented in the Add Tables dialog box. It was only when I
checked "System tables" in Options was I presented with the workbook
tabs/worksheets. *But even there some of the worksheets were quoted,
while a few were not. *All had trailing '$' appended to the name,
while some had a '_' after the quote as well. *e.g., 'dbAll$' vs.
'dbAll$'_.

Is there any source that explains the meaning of all this in one
place?

-- Roy Zider

http://support.microsoft.com/kb/257819/en-us
How To Use ADO with Excel Data from Visual Basic or VBA
Query Table Information
Of the various objects available in a relational database (tables,
views, stored procedures, and so forth), an Excel data source exposes
only table equivalents, consisting of the worksheets and the named
ranges defined in the specified workbook. Named ranges are treated as
"Tables" and worksheets are treated as "System Tables," and there is
not much useful table information you can retrieve beyond this
"table_type" property

Excel tables vs. system tables
 

Errr, I meant...

http://support.microsoft.com/kb/286891


On Jan 19, 5:22*pm, FUBARinSFO wrote:
Hi:

Yesterday I had the frustration of trying to figure out why there were
no tables listed by MSQuery/Microsoft Query when I tried to import
external data from one Excel workbook to another Excel workbook. *I
finally came across the answer that I had to have "Sytem Tables"
checked in the "Add Tables" dialog box in order to access the
worksheets in my source workbook.

This left the question, what are "system tables" and how (or why) are
they different than "tables"? *Some extensive poking around today led
me to the below Microsoft KB article.

It seems that there is simply a semantic distinction here between
worksheets in a workbook and named ranges in a workbook. *Evidently,
the worksheets themselves (as objects) are "system tables", whereas
named ranges are "tables". *Is that about right?

Since there were no named ranges in my source workbook, there were no
"tables" presented in the Add Tables dialog box. It was only when I
checked "System tables" in Options was I presented with the workbook
tabs/worksheets. *But even there some of the worksheets were quoted,
while a few were not. *All had trailing '$' appended to the name,
while some had a '_' after the quote as well. *e.g., 'dbAll$' vs.
'dbAll$'_.

Is there any source that explains the meaning of all this in one
place?

-- Roy Zider

http://support.microsoft.com/kb/257819/en-us
How To Use ADO with Excel Data from Visual Basic or VBA
Query Table Information
Of the various objects available in a relational database (tables,
views, stored procedures, and so forth), an Excel data source exposes
only table equivalents, consisting of the worksheets and the named
ranges defined in the specified workbook. Named ranges are treated as
"Tables" and worksheets are treated as "System Tables," and there is
not much useful table information you can retrieve beyond this
"table_type" property

Excel tables vs. system tables
 

http://support.microsoft.com/kb/286891

.. Click OK. In the Query Wizard - Choose Columns dialog box, and then
click Options.
10. In the Table Options dialog box, click to select the System Tables
check box, and then click OK.

Note that now you can see and select individual worksheets in your
Excel data source.

ilia:

Quote above does refer to the same problem, with about the same
solution as posted elsewhe you have to select System Tables in
order to display the tables.

I think as a matter of policy it would probably be best to define as a
named range any data table that was to be accessed externally, Then
you'd be presented with only the external-oriented data, and not the
tabs in the whole workbook.

Thanks for the pointer to the KB article.

-- Roy Zider