QueryTable Element (Microsoft Excel)

Contains the properties of a query table in a worksheet.

Contained In

ExcelWorksheet

Subelements

AutoFormatAlignment, AutoFormatBorder, AutoFormatFont, AutoFormatName, AutoFormatNumber, AutoFormatPatterns, AutoFormatWidth, DisableEdit, DisableRefresh, Filled, InsertEntireRows, Name, NewAsync, NoAutoFit, NoPreserveFormatting, NoSaveData, NoTitles, OverwriteCells, QuerySource, RefreshInfo, RefreshOnFileOpen, RowNumbers, Synchronous

Remarks

The AutoFormatName, Location, Name, RefreshInfo, and QuerySource subelements are required, but all other subelements are optional.

Example

This example obtains 6 currency rates from a Web page. The number, border, font, pattern, alignment, and width settings are affected when the predefined Color3 cell format is applied. Editing and background refreshes are disabled. The query table is refreshed when the workbook is opened, and automatically refreshed every 20 minutes.

 <x:QueryTable>
  <x:RefreshOnFileOpen/>
  <x:DisableEdit/>
  <x:DisableRefresh/>
  <x:Filled/>
  <x:InsertEntireRows/>
  <x:NoSaveData/>
  <x:Name>Currency Rates</x:Name>
  <x:AutoFormatNumber/>
  <x:AutoFormatBorder/>
  <x:AutoFormatFont/>
  <x:AutoFormatPatterns/>
  <x:AutoFormatAlignment/>
  <x:AutoFormatWidth/>
  <x:AutoFormatName>Color3</x:AutoFormatName>
  <x:QuerySource>
   <x:QueryType>Web</x:QueryType>
   <x:EntirePage/>
   <x:URLString
    HRef="http://stocks/quotes.asp?symbol=/ady,/bpy,/cdy,/zey,/dmy,/sfy"/>
   <x:RefreshTimeSpan>20</x:RefreshTimeSpan>
  </x:QuerySource>
 </x:QueryTable>

The next example creates a query table containing the product ID, name, supplier, and price from the Alphabetical List of Products table in the Northwind database of Microsoft Access. The font and pattern formats are affected when a predefined cell format is applied. Row numbers are included in the query. The query table is refreshed when the workbook is opened, and automatically refreshed every 30 minutes. Formulas to the left of the query table are filled down if more rows are returned during the refresh. The extra rows are deleted if less rows are returned.

 <x:QueryTable>
  <x:RowNumbers/>
  <x:RefreshOnFileOpen/>
  <x:Filled/>
  <x:NewAsync/>
  <x:Name>Products</x:Name>
  <x:AutoFormatFont/>
  <x:AutoFormatPatterns/>
  <x:NoTitles/>
  <x:Synchronous/>
  <x:QuerySource>
   <x:Connection>DSN=MS Access Database;DBQ=C:\Program Files\Microsoft </x:Connection>
   <x:Connection>Office\Office\Samples\Northwind.mdb;DefaultDir=C:\Program Files\</x:Connection>
   <x:Connection>Microsoft Office\Office\Samples;DriverId=25;FIL=MS </x:Connection>
   <x:Connection>Access;MaxBufferSize=2048;PageTimeout=5;</x:Connection>
   <x:CommandText>SELECT ProductID, SupplierID, ProductName, UnitPrice, UnitsInStock </x:CommandText>
   <x:CommandText>FROM `C:\Program Files\Microsoft Office\Office\</x:CommandText>
   <x:CommandText>Samples\Northwind`.`Alphabetical List of Products` </x:CommandText>
   <x:CommandText>WHERE (ProductID>10) ORDER BY ProductName</x:CommandText>
   <x:RefreshTimeSpan>30</x:RefreshTimeSpan>
  </x:QuerySource>
  <x:RefreshInfo>
   <x:NextId>13</x:NextId>
   <x:ColumnInfo>
    <x:RowNumbers/>
    <x:Id>12</x:Id>
   </x:ColumnInfo>
   <x:ColumnInfo>
    <x:Id>1</x:Id>
    <x:Name>ProductID</x:Name>
   </x:ColumnInfo>
   <x:ColumnInfo>
    <x:Id>2</x:Id>
    <x:Name>ProductName</x:Name>
   </x:ColumnInfo>
   <x:ColumnInfo>
    <x:Id>3</x:Id>
    <x:Name>SupplierID</x:Name>
   </x:ColumnInfo>
   <x:ColumnInfo>
    <x:Id>6</x:Id>
    <x:Name>UnitPrice</x:Name>
   </x:ColumnInfo>
   <x:ColumnInfo>
    <x:Id>7</x:Id>
    <x:Name>UnitsInStock</x:Name>
   </x:ColumnInfo>
  </x:RefreshInfo>
 </x:QueryTable>

The following example imports data from the text file called pp2028h.txt. The font and pattern formats are affected when a predefined cell format is applied. The decimal separator is a comma, and the thousand separator is a period. The third and seventh columns contain dates, the fourth column is skipped, the fifth column contains data of various data types, and the remaining columns are text. Tabs, space characters, commas, semicolons, and vertical bars (|) are used as delimiters. Consecutive delimiters are treated as one. The query table is refreshed when the workbook is opened, and automatically refreshed every 60 minutes.

 <x:QueryTable>
  <x:RefreshOnFileOpen/>
  <x:Name>pp2028h</x:Name>
  <x:AutoFormatFont/>
  <x:AutoFormatPatterns/>
  <x:OverwriteCells/>
  <x:NoAutoFit/>
  <x:NoSaveData/>
  <x:NoPreserveFormatting/>
  <x:QuerySource>
   <x:QueryType>Text</x:QueryType>
   <x:DoNotPromptForFile/>
   <x:TextWizardSettings>
    <x:Name HRef="c:\my documents\pp2028h.txt"/>
    <x:Decimal>,</x:Decimal>
    <x:ThousandSeparator>.</x:ThousandSeparator>
    <x:FormatSettings>
     <x:FieldType>Text</x:FieldType>
     <x:FieldType>Text</x:FieldType>
     <x:FieldType>MDY</x:FieldType>
     <x:FieldType>Skip</x:FieldType>
     <x:FieldType>AutoFormat</x:FieldType>
     <x:FieldType>Text</x:FieldType>
     <x:FieldType>DYM</x:FieldType>
    </x:FormatSettings>
    <x:Delimiters>
     <x:Tab/>
     <x:Space/>
     <x:Comma/>
     <x:Semicolon/>
     <x:Consecutive/>
     <x:Custom>|</x:Custom>
    </x:Delimiters>
   </x:TextWizardSettings>
   <x:RefreshTimeSpan>60</x:RefreshTimeSpan>
  </x:QuerySource>
 </x:QueryTable>

The next example imports stock quotes from a Web page into a query table called Stock Quotes. The query originates from Excel 97. Data from table1, table2, and the first through the third tables in the Web page are imported. All HTML formatting is imported, but the text data is not converted to columns. The column widths are affected when the predefined Simple format is applied. Column titles are not included and formatting is not preserved.

 <x:QueryTable>
  <x:NoTitles/>
  <x:NoPreserveFormatting/>
  <x:Name>Stock Quotes</x:Name>
  <x:AutoFormatName>Simple</x:AutoFormatName>
  <x:AutoFormatWidth/>
  <x:QuerySource>
   <x:QueryType>Web</x:QueryType>
   <x:NoTextToColumns/>
   <x:Query97/>
   <x:HTMLFormat>All</x:HTMLFormat>
   <x:URLString HRef="http://stocks/cgi-bin/exceldow.exe?"/>
   <x:HTMLTables>
    <x:Text>table1</x:Text>
    <x:Number>1</x:Number>
    <x:Text>table2</x:Text>
    <x:Number>2</x:Number>
    <x:Number>3</x:Number>
   </x:HTMLTables>
  </x:QuerySource>
 </x:QueryTable>

The following example obtains data from an OLE DB provider. The SQL query returns all columns from the authors table on server MSDASQL.1, and the connection is maintained after refresh. The data goes into a query table called oledb. The cell alignment, border, font, number, pattern, and widths are affected when the predefined Classic3 format is applied. Column titles are included and formatting is not preserved. Formulas to the left of the query table are filled down or deleted if more or less rows are returned during the refresh.

 <x:QueryTable>
  <x:Filled/>
  <x:NoPreserveFormatting/>
  <x:Name>oledb</x:Name>
  <x:AutoFormatName>Classic3</x:AutoFormatName>
  <x:AutoFormatNumber/>
  <x:AutoFormatBorder/>
  <x:AutoFormatFont/>
  <x:AutoFormatPatterns/>
  <x:AutoFormatAlignment/>
  <x:AutoFormatWidth/>
  <x:QuerySource>
   <x:QueryType>OLEDB</x:QueryType>
   <x:CommandText>select * from authors</x:CommandText>
   <x:CommandType>SQL</x:CommandType>
   <x:Connection>Provider=MSDASQL.1;Extended Properties="<DRIVER=SQL Server;/x:Connection>
   <x:Connection>SERVER=xltsql;UID=auto;DATABASE=dbtest"</x:Connection>
   <x:Maintain/>
  </x:QuerySource>
  <x:RefreshInfo>
   <x:NextId>11</x:NextId>
   <x:ColumnInfo>
    <x:Id>1</x:Id>
    <x:Name>au_id</x:Name>
   </x:ColumnInfo>
   <x:ColumnInfo>
    <x:Id>2</x:Id>
    <x:Name>au_lname</x:Name>
   </x:ColumnInfo>
   <x:ColumnInfo>
    <x:Id>3</x:Id>
    <x:Name>au_fname</x:Name>
   </x:ColumnInfo>
   <x:ColumnInfo>
    <x:Id>4</x:Id>
    <x:Name>phone</x:Name>
   </x:ColumnInfo>
   <x:ColumnInfo>
    <x:User/>
    <x:FillDown/>
    <x:Id>10</x:Id>
   </x:ColumnInfo>
   <x:ColumnInfo>
    <x:Id>5</x:Id>
    <x:Name>address</x:Name>
   </x:ColumnInfo>
   <x:ColumnInfo>
    <x:Id>6</x:Id>
    <x:Name>city</x:Name>
   </x:ColumnInfo>
   <x:ColumnInfo>
    <x:Id>7</x:Id>
    <x:Name>state</x:Name>
   </x:ColumnInfo>
   <x:ColumnInfo>
    <x:Id>8</x:Id>
    <x:Name>zip</x:Name>
   </x:ColumnInfo>
   <x:ColumnInfo>
    <x:Id>9</x:Id>
    <x:Name>contract</x:Name>
   </x:ColumnInfo>
  </x:RefreshInfo>
 </x:QueryTable>

The following example obtains data from Microsoft SQL Server using Microsoft Query. The SQL query returns the specified columns from the authors table on server xltsql where the state parameter matches what the user enters. The data goes into a query table called Query from xltsql. The cell font and pattern are affected when a predefined format is applied.

 <x:QueryTable>
  <x:Name>Query from xltsql</x:Name>
  <x:AutoFormatFont/>
  <x:AutoFormatPatterns/>
  <x:QuerySource>
   <x:Connection>DRIVER=SQL Server;SERVER=xltsql;</x:Connection>
   <x:Connection>UID=tester;;APP=Microsoft® Query;WSID=XLTYM9000</x:Connection>
   <x:CommandText>SELECT authors.au_id, authors.au_lname, authors.au_fname, </x:CommandText>
   <x:CommandText>authors.phone, authors.address, authors.city, </x:CommandText>
   <x:CommandText>authors.state, authors.zip, authors.contract </x:CommandText>
   <x:CommandText>FROM pubs2.dbo.authors authors WHERE (authors.state=?)</x:CommandText>
   <x:Parameter>
    <x:Name>Enter state</x:Name>
    <x:SQLType>Character</x:SQLType>
    <x:PromptString>Enter State</x:PromptString>
    <x:NonDefaultName/>
   </x:Parameter>
  </x:QuerySource>
  <x:RefreshInfo>
   <x:NextId>10</x:NextId>
   <x:ColumnInfo>
    <x:Id>1</x:Id>
    <x:Name>au_id</x:Name>
   </x:ColumnInfo>
   <x:ColumnInfo>
    <x:Id>2</x:Id>
    <x:Name>au_lname</x:Name>
   </x:ColumnInfo>
   <x:ColumnInfo>
    <x:Id>3</x:Id>
    <x:Name>au_fname</x:Name>
   </x:ColumnInfo>
   <x:ColumnInfo>
    <x:Id>4</x:Id>
    <x:Name>phone</x:Name>
   </x:ColumnInfo>
   <x:ColumnInfo>
    <x:Id>5</x:Id>
    <x:Name>address</x:Name>
   </x:ColumnInfo>
   <x:ColumnInfo>
    <x:Id>6</x:Id>
    <x:Name>city</x:Name>
   </x:ColumnInfo>
   <x:ColumnInfo>
    <x:Id>7</x:Id>
    <x:Name>state</x:Name>
   </x:ColumnInfo>
   <x:ColumnInfo>
    <x:Id>8</x:Id>
    <x:Name>zip</x:Name>
   </x:ColumnInfo>
   <x:ColumnInfo>
    <x:Id>9</x:Id>
    <x:Name>contract</x:Name>
   </x:ColumnInfo>
  </x:RefreshInfo>
 </x:QueryTable>