Creating Dynamic Reports with Query Tables in Excel

  • Article

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.