Creating Dynamic Reports with Query Tables in Excel
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
The CopyFromRecordset and TransferSpreadsheet methods are fine for importing or exporting external data that doesn't change frequently. However, if you're creating reports in Excel to present data that needs to be updated often, you may want to create a query table. A query table is a table in an Excel worksheet that's linked to an external data source, such as a SQL Server database, a Microsoft Access database, a Web page, or a text file. To retrieve the most up-to-date data, the user can refresh the query table.
Creating a Query Table by Using a Database Query
Excel creates a query table in a worksheet when you create a new database query and returns the data to that worksheet. To create a new database query from the Excel user interface, click Get External Data on the Tools menu, and then click New Database Query. In the Choose Data Source dialog box, select an existing data source or create a new one. You can then use either the Query Wizard or the Microsoft Query grid to create the database query and return the data to a worksheet. Once you've retrieved the data, click Refresh on the Tools menu to refresh the query table.
To create a new query table from VBA, use the Add method of the QueryTables collection. The QueryTables collection belongs to a Worksheet object and contains all of the QueryTable objects for that worksheet. Once you've created a new query table, you must use the Refresh method of the QueryTable object to display data in the query table. If you don't use the Refresh method, the query table will not display any data. The following example creates a query table on a new worksheet.
Function CreateQueryTable(strConnect As String, _ strSQL As String) As Boolean ' Create query table from external data source. ' Takes a valid ADO connection string and a ' valid SQL SELECT statement. Dim cnnConnect As ADODB.Connection Dim rstData As ADODB.Recordset Dim qtbData As Excel.QueryTable Dim wksNew As Excel.Worksheet On Error GoTo CreateQueryTable_Err ' Open connection on data source. Set cnnConnect = New ADODB.Connection cnnConnect.Open strConnect ' Open Recordset object on connection. Set rstData = New ADODB.Recordset rstData.Open strSQL, cnnConnect, adOpenForwardOnly ' Add new worksheet. Set wksNew = ThisWorkbook.Worksheets.Add ' Create query table in new worksheet. Set qtbData = _ wksNew.QueryTables.Add(rstData, wksNew.Range("A1")) ' Refresh query table to display data. qtbData.Refresh CreateQueryTable = True CreateQueryTable_End: On Error Resume Next rstData.Close Set rstData = Nothing Exit Function CreateQueryTable_Err: CreateQueryTable = False MsgBox "Error: " & Err.Number & vbCrLf & Err.Description Resume End_CreateQueryTable End Function
The CreateQueryTable procedure appears in the modQueryTables module in the Northwind.xls sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH15 subfolder on the Office 2000 Developer CD-ROM.
If your solution is an Access-based solution, and you want to create dynamic reports in Excel, you can use Automation to create a query table or PivotTable report in Excel from code running in Access. Using Automation gives you more control over the process of transferring data from a database to Excel. You can also use Automation to format the report, filter data, create charts, and so on, so that the user simply clicks a button in Access to bring up a formatted report in Excel.
Creating a Query Table by Using a Web Query
You can also create a query table from data on a Web page. In this case, you specify one of the following for the Connection argument of the Add method of the QueryTables collection: either a Web page address, or the path and file name of a saved Web query (.iqy). The following code fragment creates a query table by using a saved query that pulls stock quotes from the Microsoft Investor Web site.
' Add new worksheet. Set wksNew = ThisWorkbook.Worksheets.Add ' Create query table from saved Web query. Set qtbQuote = wksNew.QueryTables.Add(Connection:= _ "FINDER;C:\Program Files\Microsoft " _ & "Office\Office\Queries\Microsoft " _ & "Investor Stock Quotes.iqy", Destination:=Range("A1")) ' Set query table properties and retrieve data. With qtbQuote .Name = "Microsoft Investor Stock Quotes_1" .FieldNames = True .PreserveFormatting = False .RefreshStyle = xlInsertDeleteCells .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingAll .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .Refresh BackgroundQuery:=False End With
This code fragment is taken from the RetrieveStockQuotes procedure, which appears in the modStockQuotes module in the StockQuotes.xls sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH15 subfolder on the Office 2000 Developer CD-ROM.