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