Class QueryTable (Excel VBA)

The class QueryTable represents a worksheet table built from data returned from an external data source, such as a SQL server or a Microsoft Access database.

The main procedures of class QueryTable are Delete and QueryTables.Add

Set

To use a QueryTable class variable it first needs to be instantiated, for example

Dim qrytbl as QueryTable
Set qrytbl = ActiveCell.QueryTable

The following procedures can be used to set variables of type QueryTable: ListObject.QueryTable, QueryTables.Item, QueryTables.Add, Range.QueryTable and Worksheet.QueryTables

For Each

Here is an example of processing the QueryTable items in a collection.

Dim qrytbl As QueryTable
For Each qrytbl In Application.QueryTables
	
Next qrytbl

Themes

Some procedures in this class have been grouped together in themes and are described on separate theme pages

Enable with its procedures EnableEditing and EnableRefresh
Row with its procedures FetchedRowOverflow, RowNumbers and TextFileStartRow
Column with its procedures PreserveColumnInfo and TextFileColumnDataTypes
Web with its procedures WebConsecutiveDelimitersAsOne, WebDisableDateRecognition, WebDisableRedirections, WebFormatting, WebPreFormattedTextToColumns, WebSelectionType and WebTables

Methods

These are the main methods of the QueryTable class

Delete - Deletes the object.

ActiveCell.QueryTable.Delete

QueryTables.Add - Creates a new query table.

Dim cnnConnect As ADODB.Connection 
Dim rstRecordset As ADODB.Recordset 
 
Set cnnConnect = New ADODB.Connection 
cnnConnect.Open "Provider=SQLOLEDB;" & _ 
    "Data Source=srvdata;" & _ 
    "User ID=testac;Password=4me2no;" 
 
Set rstRecordset = New ADODB.Recordset 
rstRecordset.Open _ 
    Source:="Select Name, Quantity, Price From Products", _ 
    ActiveConnection:=cnnConnect, _ 
    CursorType:=adOpenDynamic, _ 
    LockType:=adLockReadOnly, _ 
    Options:=adCmdText 
 
With ActiveSheet.QueryTables.Add( _ 
        Connection:=rstRecordset, _ 
        Destination:=Range("A1")) 
    .Name = "Contact List" 
    .FieldNames = True 
    .RowNumbers = False 
    .FillAdjacentFormulas = False 
    .PreserveFormatting = True 
    .RefreshOnFileOpen = False 
    .BackgroundQuery = True 
    .RefreshStyle = xlInsertDeleteCells 
    .SavePassword = True 
    .SaveData = True 
    .AdjustColumnWidth = True 
    .RefreshPeriod = 0 
    .PreserveColumnInfo = True 
    .Refresh BackgroundQuery:=False 
End With

Other Methods

CancelRefresh - Cancels all background queries for the specified query table. Use the Refreshing property to determine whether a background query is currently in progress.

Refresh - Updates an external data range in a QueryTable object.

ResetTimer - Resets the refresh timer for the specified query table or PivotTable report to the last interval that you set by using the RefreshPeriod property.

SaveAsODC - Saves the QueryTable cache source as a Microsoft Office Data Connection file.

QueryTables.Item - Returns a single object from a collection.

Properties

AdjustColumnWidth true if the column widths are automatically adjusted for the best fit each time you refresh the specified query table. False if the column widths are not automatically adjusted with each refresh. The default value is True.

BackgroundQuery true if queries for the query table are performed asynchronously (in the background).

CommandText returns or sets the command string for the specified data source. Read/write Variant.

CommandType returns or sets one of these XlCmdType constants: xlCmdCube, xlCmdDefault, xlCmdSql, or xlCmdTable. The constant that is returned or set describes the value of the CommandText property. The default value is xlCmdSQL.

Connection returns or sets a string that contains one of the following:.

Destination returns the cell in the upper-left corner of the query table destination range (the range where the resulting query table will be placed). The destination range must be on the worksheet that contains the QueryTable object.

EditWebPage returns or sets the webpage Uniform Resource Locator (URL) for a web query.

FieldNames true if field names from the data source appear as column headings for the returned data. The default value is True.

FillAdjacentFormulas true if formulas to the right of the specified query table are automatically updated whenever the query table is refreshed.

ListObject returns a ListObject object for the QueryTable object.

MaintainConnection true if the connection to the specified data source is maintained after the refresh and until the workbook is closed. The default value is True.

Name returns or sets a String value representing the name of the object.

Parameters returns a Parameters collection that represents the query table parameters.

Parent returns the parent object for the specified object. Read-only.

PostText returns or sets the string used with the post method of inputting data into a web server to return data from a web query.

PreserveFormatting true if any formatting common to the first five rows of data are applied to new rows of data in the query table. Unused cells aren't formatted. The property is False if the last AutoFormat applied to the query table is applied to new rows of data. The default value is True.

QueryType indicates the type of query used by Microsoft Excel to populate the query table.

Recordset

Refreshing true if there is a background query in progress for the specified query table.

RefreshOnFileOpen true if the PivotTable cache or query table is automatically updated each time the workbook is opened. The default value is False.

RefreshPeriod returns or sets the number of minutes between refreshes.

RefreshStyle returns or sets the way rows on the specified worksheet are added or deleted to accommodate the number of rows in a recordset returned by a query.

ResultRange returns a Range object that represents the area of the worksheet occupied by the specified query table.

RobustConnect returns or sets how the query table connects to its data source.

SaveData true if data for the QueryTable report is saved with the workbook. False if only the report definition is saved.

SavePassword true if password information in an ODBC connection string is saved with the specified query. False if the password is removed.

Sort returns the sort criteria for the query table range.

SourceConnectionFile returns or sets a String indicating the Microsoft Office Data Connection file or similar file that was used to create the QueryTable.

SourceDataFile returns or sets a String value that indicates the source data file for a query table.

TextFileCommaDelimiter true if the comma is the delimiter when you import a text file into a query table. False if you want to use some other character as the delimiter. The default value is False.

TextFileConsecutiveDelimiter true if consecutive delimiters are treated as a single delimiter when you import a text file into a query table. The default value is False.

TextFileDecimalSeparator returns or sets the decimal separator character that Microsoft Excel uses when you import a text file into a query table. The default is the system decimal separator character.

TextFileFixedColumnWidths returns or sets an array of integers that correspond to the widths of the columns (in characters) in the text file that you are importing into a query table. Valid widths are from 1 through 32767 characters.

TextFileOtherDelimiter returns or sets the character used as the delimiter when you import a text file into a query table. The default value is null.

TextFileParseType returns or sets the column format for the data in the text file that you are importing into a query table.

TextFilePlatform returns or sets the origin of the text file that you are importing into the query table. This property determines which code page is used during the data import.

TextFilePromptOnRefresh true if you want to specify the name of the imported text file each time the query table is refreshed. The Import Text File dialog box allows you to specify the path and file name. The default value is False.

TextFileSemicolonDelimiter true if the semicolon is the delimiter when you import a text file into a query table, and if the value of the TextFileParseType property is xlDelimited. The default value is False.

TextFileSpaceDelimiter true if the space character is the delimiter when you import a text file into a query table. The default value is False.

TextFileTabDelimiter true if the tab character is the delimiter when you import a text file into a query table. The default value is False.

TextFileTextQualifier returns or sets the text qualifier when you import a text file into a query table. The text qualifier specifies that the enclosed data is in text format.

TextFileThousandsSeparator returns or sets the thousands separator character that Microsoft Excel uses when you import a text file into a query table. The default is the system thousands separator character.

TextFileTrailingMinusNumbers true for Microsoft Excel to treat numbers imported as text that begin with a - (minus) symbol as a negative symbol. False for Excel to treat numbers imported as text that begin with a - symbol as text.

TextFileVisualLayout returns or sets an XlTextVisualLayoutType enumeration that indicates whether the visual layout of the text being imported is left-to-right or right-to-left.

WebSingleBlockTextImport true if data from the HTML

 tags on the specified webpage is processed all at once when you import the page into a query table. False if the data is imported in blocks of contiguous rows so that header rows will be recognized as such. The default value is False.

WorkbookConnection returns the WorkbookConnection object that the query table uses.

QueryTables.Count returns a Long value that represents the number of objects in the collection.

QueryTables.Parent returns the parent object for the specified object. Read-only.