Class QueryTables (Excel VBA)

A collection of QueryTable objects. To use a QueryTables class variable it first needs to be instantiated, for example

Dim qts as QueryTables
Set qts = ActiveSheet.QueryTables

For Each

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

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

Add

Creates a new query table.

A query created by this method isn't run until the Refresh method is called.

Add (Connection, Destination, Sql)

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

Arguments

The following arguments are required:

Connection (String) - The data source for the query table. Can be one of the following: A string containing an OLE DB or ODBC connection string. The ODBC connection string has the form ODBC;. A QueryTable object from which the query information is initially copied, including the connection string and the SQL text, but not including the Destination range. Specifying a QueryTable object causes the Sql argument to be ignored. An ADO or DAO Recordset object. Data is read from the ADO or DAO recordset. Microsoft Excel retains the recordset until the query table is deleted or the connection is changed. The resulting query table cannot be edited. A web query. A string in the form URL; where URL; is required but not localized and the rest of the string is used for the URL of the web query. Data Finder. A string in the form FINDER; where FINDER; is required but not localized. The rest of the string is the path and file name of a Data Finder file (*.dqy or *.iqy). The file is read when the Add method is run; subsequent calls to the Connection property of the query table will return strings beginning with ODBC; or URL; as appropriate. A text file. A string in the form TEXT; where TEXT; is required but not localized.

Destination (Range) - 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 QueryTables object specified by expression.

Optional arguments

The following argument is optional

Sql (QueryTable) - The SQL query string to be run on the ODBC data source. This argument is optional when you are using an ODBC data source (if you don't specify it here, you should set it by using the Sql property of the query table before the table is refreshed). You cannot use this argument when a QueryTable object, text file, or ADO or DAO Recordset object is specified as the data source.

Count

Returns a Long value that represents the number of objects in the collection.

Dim lngCount As Long
lngCount = ActiveSheet.QueryTables.Count

Item

Returns a single object from a collection.

Item (Index)

Index: The name or index number for the object.

Dim qrytbl As QueryTable
Set qrytbl = ActiveSheet.QueryTables(Index:=1)