Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
concatenate tables into html tables | Excel Discussion (Misc queries) | |||
concatenate tables into html tables | New Users to Excel | |||
Building pivot tables in Excel 2007 based on existing pivot tables? | Excel Discussion (Misc queries) | |||
Any way to programmatically make pivot tables behave more like data tables? | Excel Programming | |||
Extracting/Exporting HTML Tables or PDF Tables into Excel | Excel Programming |