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.
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
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext