QueryTable

Represents a worksheet table built from data returned from an external data source, such as an SQL server or a Microsoft Access database.
The QueryTable object is a member of the QueryTables collection.


Use QueryTables(index), where index is the index number of the query table, to return a single QueryTable object.refreshed.
The following example sets query table one so that formulas to the right of it are automatically updated whenever it's


Sheets("sheet1").QueryTables(1).FillAdjacentFormulas = True 


QueryType property

Indicates the type of query used by Microsoft Excel to populate the query table or PivotTable cache. Read-only XlQueryType.
You specify the data source in the prefix for the Connection property's value.

Sheets("sheet1").QueryTables(1).QueryType = xlQueryType.xlADORecordset 

Different Types of Query

ADO Recordset
DAO Recordset
ODBC Query
OLE DB Query
Text Import
Web Query


Sub Add_QueryTable() 
  Dim cnt As ADODB.Connection
  Dim rst As ADODB.Recordset
 
  Set wbBook = ActiveWorkbook
  Set wsSheet = wbBook.Worksheets(1)
 
  With wsSheet
    Set rnStart = .Range("A1")
  End With
 
  stSQL = "SELECT * FROM Shippers;"
 
  Set cnt = New ADODB.Connection
 
  With cnt
    .CursorLocation = adUseClient
    .Open stADO
    Set rst = .Execute(stSQL)
  End With
 
'Here we add the Recordset to the QueryTable.
  Set qData = wsSheet.QueryTables.Add(rst, rnStart)
 
'In order to view any data the QueryTable need to
'be refreshed.
  qData.Refresh
 
  rst.Close
  cnt.Close
  Set rst = Nothing
  Set cnt = Nothing
 
End Sub

AdjustColumnWidth property

True if the column widths are automatically adjusted for the best fit each time you refresh the specified query table or XML map.
False if the column widths aren't automatically adjusted with each refresh. The default value is True. Read/write Boolean.
The maximum column width is two-thirds the width of the screen.
This example turns off automatic column-width adjustment for the newly added query table on the first worksheet in the first workbook.

With Workbooks(1).Worksheets(1).QueryTables _ 
    .Add(Connection:=varDBConnStr, _
        Destination:=Range("B1"), _
        Sql:="Select Price From CurrentStocks " & _
            "Where Symbol = 'MSFT'")
    .AdjustColumnWidth = False
    .Refresh
End With

BackgroundQuery property

True if queries for the PivotTable report or query table are performed asynchronously (in the background). Read/write Boolean.
For OLAP data sources, this property is read-only and always returns False.


This example causes queries for the first PivotTable report on worksheet one to be performed in the background.


Worksheets(1).PivotTables("Pivot1").PivotCache.BackgroundQuery = True 


CancelRefresh Method

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


Delete Method

Deletes the object.



Destination property

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. Read-only Range.
This example scrolls through the active window until the upper-left corner of query table one is in the upper-left corner of the window.

Set d = Worksheets(1).QueryTables(1).Destination 
With ActiveWindow
    .ScrollColumn = d.Column
    .ScrollRow = d.Row
End With

EnableEditing property

True if the user can edit the specified query table. False if the user can only refresh the query table. Read/write Boolean.


EnableRefresh property

True if the PivotTable cache or query table can be refreshed by the user. The default value is True. Read/write Boolean.
The RefreshOnFileOpen property is ignored if the EnableRefresh property is set to False.
For OLAP data sources, setting this property to False disables updates.


FetchedRowOverflow property

True if the number of rows returned by the last use of the Refresh method is greater than the number of rows available on the worksheet. Read-only Boolean.

With Worksheets(1).QueryTables(1) 
    .Refresh
    If .FetchedRowOverflow Then
        MsgBox "Query too large: please redefine."
    End If
End With

FieldNames property

True if field names from the data source appear as column headings for the returned data. The default value is True. Read/write Boolean.


FillAdjacentFormulas property

True if formulas to the right of the specified query table are automatically updated whenever the query table is refreshed. Read/write Boolean.



ListObject property

Returns a ListObject object for the Range object or QueryTable object. Read-only ListObject object.


MaintainConnection property

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. Read/write Boolean.
You can set the MaintainConnection property only if the QueryType property of the query table or PivotTable cache is set to xlOLEDBQuery.
If you anticipate frequent queries to a server, setting this property to True might improve performance by reducing reconnection time. Setting the property to False causes an open connection to be closed.



Name Property

Returns or sets the name of the object. Read/write String.



Parameters property

Returns a Parameters collection that represents the query table parameters. Read-only.



PreserveColumnInfo property

True if column sorting, filtering, and layout information is preserved whenever a query table is refreshed. The default value is False. Read/write Boolean.
This property has an effect only when the query table is using a database connection.
You can set this property to False for compatibility with earlier versions of Microsoft Excel.



PreserveFormatting property

this property is 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 (unless the query table was created in Microsoft Excel 97 and the HasAutoFormat property is True, in which case PreserveFormatting is False).
For database query tables, the default formatting setting is xlSimple.
The new AutoFormat style is applied to the query table when the table is refreshed. The AutoFormat is reset to None whenever PreserveFormatting is set to False. As a result, any AutoFormat that's set before PreserveFormatting is set to False and before the query table is refreshed doesn't take effect, and the resulting query table has no formatting applied to it.


This example demonstrates how setting PreserveFormatting to False causes the AutoFormat to be set to xlRangeAutoFormatNone instead of the specified xlRangeAutoFormatColor1 format.


With Workbooks(1).Worksheets(1).QueryTables(1) 
    .Range.AutoFormat = xlRangeAutoFormatColor1
    .PreserveFormatting = False
    .Refresh
End With

Recordset property

Returns or sets a Recordset object that's used as the data source for the specified query table or PivotTable cache. Read/write.
If this property is used to overwrite an existing recordset, the change takes effect when the Refresh method is run.


Refresh Method

Updates an external data range (QueryTable). Boolean.

expression.Refresh(BackgroundQuery) 

BackgroundQuery Optional Variant. Used only with QueryTables that are based on the results of a SQL query. True to return control to the procedure as soon as a database connection is made and the the query is submitted. The QueryTable is updated in the background. False to return control to the procedure only after all data has been fetched to the worksheet. If this argument isn't specified, the setting of the BackgroundQuery property determines the query mode.


The following remarks apply to QueryTable objects that are based on the results of a SQL query.


The Refresh method causes Microsoft Excel to connect to the data source of the QueryTable object, execute the SQL query, and return data to the range that is based on the QueryTable object. Unless this method is called, the QueryTable object doesn't communicate with the data source.


When making the connection to the OLE DB or ODBC data source, Microsoft Excel uses the connection string specified by the Connection property. If the specified connection string is missing required values, dialog boxes will be displayed to prompt the user for the required information. If the DisplayAlerts property is False, dialog boxes aren't displayed and the Refresh method fails with the Insufficient Connection Information exception.


After Microsoft Excel makes a successful connection, it stores the completed connection string so that prompts won't be displayed for subsequent calls to the Refresh method during the same editing session. You can obtain the completed connection string by examining the value of the Connection property.


After the database connection is made, the SQL query is validated. If the query isn't valid, the Refresh method fails with the SQL Syntax Error exception.


If the query requires parameters, the Parameters collection must be initialized with parameter binding information before the Refresh method is called. If not enough parameters have been bound, the Refresh method fails with the Parameter Error exception. If parameters are set to prompt for their values, dialog boxes are displayed to the user regardless of the setting of the DisplayAlerts property. If the user cancels a parameter dialog box, the Refresh method halts and returns False. If extra parameters are bound with the Parameters collection, these extra parameters are ignored.


The Refresh method returns True if the query is successfully completed or started; it returns False if the user cancels a connection or parameter dialog box.


To see whether the number of fetched rows exceeded the number of available rows on the worksheet, examine the FetchedRowOverflow property. This property is initialized every time the Refresh method is called.



This example refreshes the PivotTable cache for the first PivotTable report on the first worksheet in a workbook.


Worksheets(1).PivotTables(1).PivotCache.Refresh 



Refreshing property

True if there's a background query in progress for the specified query table. Read/write Boolean.
Use the CancelRefresh method to cancel background queries.



RefreshOnFileOpen property

True if the PivotTable cache or query table is automatically updated each time the workbook is opened. The default value is False. Read/write Boolean.
Query tables and PivotTable reports are not automatically refreshed when you open the workbook by using the Open method in Visual Basic. Use the Refresh method to refresh the data after the workbook is open.



RefreshPeriod property

Returns or sets the number of minutes between refreshes. Read/write Long.
Setting the period to 0 (zero) disables automatic timed refreshes and is equivalent to setting this property to Null.
The value of the RefreshPeriod property can be an integer from 0 through 32767.



RefreshStyle property

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. Read/write XlCellInsertionMode.



ResultRange property

Returns a Range object that represents the area of the worksheet occupied by the specified query table. Read-only.
The range doesn't include the field name row or the row number column.


RobustConnect property

Returns or sets how the PivotTable cache connects to its data source. Read/write XlRobustConnect.



RowNumbers property

True if row numbers are added as the first column of the specified query table. Read/write Boolean.
Setting this property to True doesn't immediately cause row numbers to appear. The row numbers appear the next time the query table is refreshed, and they're reconfigured every time the query table is refreshed.



SaveData property

True if data for the PivotTable report is saved with the workbook. False if only the report definition is saved. Read/write Boolean.
For OLAP data sources, this property is always set to False.



SavePassword property

True if password information in an ODBC connection string is saved with the specified query. False if the password is removed. Read/write Boolean.
This property affects only ODBC queries.



SourceConnectionFile property

Returns or sets a String indicating the Microsoft Office Data Connection file or similar file that was used to create the PivotTable. Read/write.


Web Query


EditWebPage property

Returns or sets the web page Uniform Resource Locator (URL) for a web query. Read/write Variant.
The EditWebPage property returns Null if not set. The EditWebPage property is only meaningful if the query type is Web or OLE.
If the EditWebPage is not null then ignore the WebTables property for refreshing. As a result an XML query and the WebTable property refers to the table in the original Web page and should only be used in the edit case to pre-populate the Web Query dialog box.
In this example, Microsoft Excel displays to the user a Web page URL. This example assumes a QueryTable object in cell A1 exists in the active worksheet and that a file called "MyHomepage.htm" exists on the C: drive.

Sub ReturnURL() 

' Set the EditWebPage property to a source.
    Range("A1").QueryTable.EditWebPage = "C:\MyHomepage.htm"

' Display the source to the user.
    MsgBox Range("A1").QueryTable.EditWebPage

End Sub



WebConsecutiveDelimitersAsOne property

True if consecutive delimiters are treated as a single delimiter when you import data from HTML <PRE> tags in a Web page into a query table, and if the data is to be parsed into columns.
False if you want to treat consecutive delimiters as multiple delimiters. The default value is True. Read/write Boolean.
Use this property only when the query table's QueryType property is set to xlWebQuery, the query returns an HTML document, and the WebPreFormattedTextToColumns property is set to True.




WebDisableDateRecognition property

True if data that resembles dates is parsed as text when you import a Web page into a query table. False if date recognition is used. The default value is False. Read/write Boolean.
Use this property only when the query table's QueryType property is set to xlWebQuery and the query returns an HTML document.



WebDisableRedirections property

True if Web query redirections are disabled for a QueryTable object. The default value is False. Read/write Boolean.



WebFormatting property

Returns or sets a value that determines how much formatting from a Web page, if any, is applied when you import the page into a query table. Read/write XlWebFormatting.
Use this property only when the query table's QueryType property is set to xlWebQuery and the query returns an HTML document.


WebPreFormattedTextToColumns property

Returns or sets whether data contained within HTML <PRE> tags in the Web page is parsed into columns when you import the page into a query table. The default is True. Read/write Boolean.
This property is used only when the QueryType property of the query table is xlWebQuery and the query returns a HTML document.



WebSelectionType property

Returns or sets a value that determines whether an entire Web page, all tables on the Web page, or only specific tables on the Web page are imported into a query table. Read/write XlWebSelectionType.
Use this property only when the query table's QueryType property is set to xlWebQuery and the query returns an HTML document.
If the value of this property is xlSpecifiedTables, you can use the WebTables property to specify the tables to be imported.



WebSingleBlockTextImport property

True if data from the HTML <PRE> tags in the specified Web page 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. Read/write Boolean.
Use this property only when the query table's QueryType property is set to xlWebQuery and the query returns an HTML document.



WebTables property

Returns or sets a comma-delimited list of table names or table index numbers when you import a Web page into a query table. Read/write String.
Use this property only when the query table's QueryType property is set to xlWebQuery, the query returns an HTML document, and the value of the WebSelectionType property is xlSpecifiedTables.



Web Queries


References of the form QueryTable.Name = "abc" are not properly executed
If there was previously a querytable with the name "abc" then a new querytable is designated by "abc_1", "abc_2" and so on.
Even if the old query table was deleted a long time ago.


What are QueryTables ?

Returns the QueryTables collection that represents all the query tables on the specified worksheet. Read-only.
Each QueryTable object represents a worksheet table built from data returned from an external data source.


Refreshing

This example refreshes all query tables on worksheet one.

For Each qt in Worksheets(1).QueryTables 
    qt.Refresh
Next

FillAdjacentFormulas

This example sets query table one so that formulas to the right of it are automatically updated whenever it's refreshed.

Sheets("sheet1").QueryTables(1).FillAdjacentFormulas = True 

QueryType

Indicates the type of query used by Microsoft Excel to populate the query table or PivotTable cache. Read-only XlQueryType .

expression.QueryType = xlQueryType.xlWebQuery. 

Connection

You specify the data source in the prefix for the Connection property's value.



Set qtQtrResults = _ 
    Workbooks(1).Worksheets(1).QueryTables(1)
With qtQtrResults
    if .QueryType = xlWebQuery Then
        .Refresh
    End If
End With

Inserting a New WebQuery

Sub CreateNewQuery() 

    Dim WSD As Worksheet
    Dim WSW As Worksheet
    Dim QT As QueryTable
    
    For m = 1 To 27
        Select Case m
            Case 27
                MyStr = "1"
            Case Else
                MyStr = Chr(64 + m)
        End Select
        MyName = "Query" & m
        ConnectString = "URL;http://www.pma-online.org/scripts/showmemlist.cfm?letter=" & MyStr
        
' Clear any existing
        For Each QT In ActiveSheet.QueryTables
            QT.Delete
        Next QT
        
' Define a new Web Query
        Set QT = ActiveSheet.QueryTables.Add(Connection:=ConnectString, Destination:=Range("A1"))
        With QT
            .Name = MyName
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = False
            .RefreshStyle = xlCellInsertionMode.xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlWebSelectionType.xlSpecifiedTables
            .WebFormatting = xlWebFormatting.xlWebFormattingAll
            .WebTables = "7"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
        End With
        
' Refresh the Query
        QT.Refresh BackgroundQuery:=True
        
    Next m
    
End Sub


Create QueryTable and populate with ADO Recordset

Returns data from the Northwind database

Sub Add_QueryTable_ADO_Recordset() 
    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim stSQL As String
     
    Dim qtData As QueryTable
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim rnStart As Range
     
     
    Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
    "Persist Security Info=False;" & _
    "Initial Catalog=Northwind;" & _
    "Data Source=IBM"
     
    Set wbBook = ActiveWorkbook
    Set wsSheet = wbBook.Worksheets(1)
     
    With wsSheet
        Set rnStart = .Range("A1")
    End With
     
    stSQL = "SELECT * FROM Shippers"
     
    Set cnt = New ADODB.Connection
     
    With cnt
        .CursorLocation = adUseClient
        .Open stADO
        .CommandTimeout = 0
        Set rst = .Execute(stSQL)
    End With
     
'Here we add the Recordset to the created QueryTable.
    Set qtData = wsSheet.QueryTables.Add(rst, rnStart)
     
'In order to view any data the QueryTable need to be refreshed.
    qData.Refresh
     
'Cleaning up.
    rst.Close
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing
     
End Sub


Dim qt As QueryTable 
sqlstring = "select 96Sales.totals from 96Sales where profit < 5"
connstring = _
    "ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;Database=96Sales"
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
        Destination:=Range("B1"), Sql:=sqlstring)
    .Refresh
End With


© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext