Web Queries and Dynamic Chart Data in Excel 2002

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.

John R. Durant
Microsoft Corporation

June 2003

Applies to:
    Microsoft® Excel 2002
    Microsoft Office Excel 2003

Summary: Learn how to use advanced Web query techniques to streamline data feeds into Microsoft Excel 2002. This article describes how to use some of the lesser-known abilities of the QueryTable object in Excel such as how to use it for importing data while sending parameters to the target Web server and how to cause a chart to change its data source after importing a new payload by using Web queries. (15 printed pages)

Contents

Business Scenario
Excel Web Queries
Dynamic Chart Data
Final Touches
Conclusion

Business Scenario

Before diving into the technical details, let's take just a moment to describe exactly what kind of problem Web queries and dynamic chart sizing can solve. Imagine a sales manager who regularly looks at sales data. She wants to specify a range of dates and then get aggregate sales data. In particular, she wants to see sales figures summarized by day within the specified range. She wants to see the raw data as well as a chart showing the summary sales trend for the specified date range.

It is not practical to create explicit reports for each of her requests. To do this, someone must sift through input data and create separate spreadsheet reports for the vast number of scenarios that could exist in the organization. The burden on the developers and IT staff is too large, especially when considering that the sales manager for one department is not the only person in the organization that has a request of this nature. A better possibility is to have an Excel spreadsheet that could adapt itself to deliver the various reports the sales manager needs as well as one that other departments could reuse and adjust for their similar needs.

This article shows you how to use features in Microsoft Excel 2002 for a simple, yet complete business solution, shown in Figure 1, that meets these needs.

Figure 1. Identify criteria and generate a dynamic report

This article discusses how to use Web queries as the primary vehicle for data delivery. You will also employ a few controls in an attractive user interface to make it easier for the user to initiate the report. Finally, you will see how to create a chart that automatically responds to changes in its source data so that it is a dynamic, living report.

Excel Web Queries

Web queries are not new to Excel 2002 or to Microsoft Office Excel 2003. Actually, they have been around since Excel 97, but with Excel 2002, they became more obvious, more powerful, and easier to use. The basic principle behind Web queries is to allow Excel seamlessly use data that is retrieved from a URL. The data can be simple HTML pages, document content, XML, or any text-based data in a URL-addressable location. What this means is that, although a Microsoft ActiveX® server control or other complex binary object on a page cannot provide its internal data directly to Excel, virtually any other URL that offers data to your organization is a candidate for direct import into Excel for reporting needs. Users can employ the Excel user interface to initiate a Web query. However, what makes things intriguing for developers is that you can also do these activities programmatically, thus allowing Web queries to become part of a more complete and reusable solution that you develop.

In our business solution, the user opens the Web query dialog box and types a URL to retrieve the target data, but we want a solution that makes things easier for the user. The focus for the user is on evaluating the data, not in knowing how to get the data. To understand how it works, look at Figure 2 that shows the application flow.

Figure 2. Retrieve data from a Web page based on specific criteria

The focus of the code, then, is where the Web query retrieves the data from a URL and places it into the spreadsheet. While we use the term Web query to refer to this technique, in the object model a QueryTable object represents a Web query, so named because it actually represents a table of data returned from a variety of external sources. A Web query is a type of QueryTable, whose source is an addressable URL. A workbook organizes by group all of these together in a QueryTables collection, so the lifetime of a QueryTable is bounded by the lifetime of a workbook. In other words, there is no way to make Web queries work in the background without an open workbook.

QueryTable Object Overview

To begin to create a Web query programmatically, you must set a couple of properties of the QueryTable object and add it to the QueryTables collection. This process is completed rather economically in a single step by calling the Add method of the QueryTables collection. The Add method returns a QueryTable object that is the new Web query. The syntax for the Add method is as follows:

QueryTables.Add(Connection,Destination)

The Connection parameter accepts a reference to whatever is going to serve up your data. If the method returns a QueryTable used to retrieve Web-based data, then the connection is a URL. Otherwise, this can be a database connection string, an ActiveX Data Objects (ADO) Recordset object, a reference to a text file, or an instance of a QueryTable whose properties are already set. Because the function of a QueryTable is to import tabular data from an external source, it needs somewhere a location to place the resulting payload. The Destination parameter is for this purpose. This parameter is a reference to a cell range where the imported data is placed. Because a QueryTable is tied to a worksheet, the range must be within the same sheet as where the QueryTable exists. The final parameter, Sql, is optional and designates a SQL-syntax query string when creating a QueryTable to an ODBC source. Web queries do not use this parameter.

These values are the only ones that are absolutely required to set any Web query. The actual connection, however, is made when calling the Refresh method of the QueryTable object. When this method is called, Excel connects to the source specified for the Connection property of the QueryTable. Keep in mind that Excel cannot broker complicated connectivity scenarios for the user. For example, if the URL to access requires a user to logon with specific credentials, the Web server prompts the user. However, Excel cannot negotiate this interaction, and the Web query fails. You should handle such results in your code rather than having various alerts unexpectedly appear to the user. The Application object has a DisplayAlerts property that can be set to FALSE during the time you are attempting to run the Web query to avoid these unexpected alerts. If the QueryTable successfully connected to the target source and retrieved data, it returns a value of TRUE, otherwise it returns FALSE. Sometimes, the Web query may retrieve more than the limit of rows allowed in an Excel spreadsheet. To determine whether this error occurred, look at the FetchedRowOverflow property of the QueryTable object. If the number of rows was too large, the property value will be TRUE. In the following example, you can see how the QueryTable is created, and how things are handled if too many rows are returned.

Dim qtsQueries as QueryTables
Dim qtQuery as QueryTable
Set qtsQueries =ActiveSheet.QueryTables
Set qtQuery = qtsQueries.Add _
  ("URL;http://server_name/site_name/my_data.htm", _      
  Application.Range("C11:D11"))
With qtQuery
  .Refresh
  If .FetchedRowOverflow Then
    MsgBox "Your request returned too many results. " _
    & "Please refine your search.", vbInformation, "Result Error"
  End If
End With

You should configure other settings on the QueryTable object so that the data imports in an expected manner. While you should consult the object model documentation for all of the properties and methods of the QueryTable object, some more commonly used members are worth noting here. To make things easier, the table organizes the members into groups for reference. The first group is a list of members that connect to data, identify which data to import, and reconnect to data sources refresh data. The second group is a list of members that format imported data.

Be assured that it is unlikely you will need to use all of these properties, methods, and events in every Web query. The QueryTable object has several additional properties and methods that are not relevant to Web queries, so they are not included here.

Returning to our solution, the target data are contained on an ASP-based Web page. That page actually retrieves the data from SQL Server based on the authenticated identity of the user. The retrieved data are actually in XML format and then transformed using XSL to become simple HTML. You can further restrict the results retrieved from SQL Server by using values in posted form variables sent to the ASP page. These values are the date range values the user supplies in the spreadsheet. The internal workings of these Web and database operations are beyond the scope of this paper. However, the fact that the Web page expects form variables means that the VBA code must accommodate them in the QueryTable definition before it attempts to connect to the data source.

Web Query Setup

The two main things you need to specify for any Web query are where to look for source data, and where to place the data in the spreadsheet after retrieval. Our scenario adds one other bit of complexity by requiring that the Web query provide some parameters, in this case a beginning and an ending date, so that it can refine scope of retrieved data. In our VBA IDE, we place our code in the Click Event procedure for a button embedded in the worksheet, cmdGetData.

This procedure launches the main code sequence to deliver the business solution. At the outset, the procedure contains a series of variable declarations and some code that defines the source of the query, the destination range, and the parameters posted to the Web page.

  Dim qtQuery As QueryTable
  Dim qtsQueries As QueryTables
  Dim nmDefinedNames As Names
  Dim rngQueryParams As Range
  Dim strPost As String
  Dim strParamName As String
  Dim i As Integer
  Dim strTargetURL As String
  
  Set qtsQueries = ActiveSheet.QueryTables()
  Set nmDefinedNames = ActiveWorkbook.Names
  strTargetURL = "URL;" & nmDefinedNames("Server").RefersToRange.Text & _
    cboReportSource.Text & nmDefinedNames("Page").RefersToRange.Text
  Set qtQuery = qtsQueries.Add(strTargetURL, _
    Application.Range("ReportSheet!C11:D11"))
  Set rngQueryParams = nmDefinedNames.Item("Parameters").RefersToRange
  For i = 0 To rngQueryParams.Rows.Count - 1
    strParamName = rngQueryParams.Rows(i)
    If i = 0 Then
      strPost = strParamName & 
        nmDefinedNames.Item(strParamName).RefersToRange.Text
    Else
      strPost = strPost & "&" & strParamName & 
        nmDefinedNames.Item(strParamName).RefersToRange.Text
    End If
  Next i

The first variable used, qtsQueries, acquires a reference to QueryTables collection from the worksheet like this: Set qtsQueries = ActiveSheet.QueryTables(). Then, add a new QueryTable instance to the collection and its reference is returned to the qtQuery object variable. In so doing, two parameters are passed to the Add method of the QueryTables collection, the target URL, contained in a variable, and a hard-coded reference to the destination range. The strTargetURL variable is constructed by concatenating values from the user interface and from named ranges on a hidden worksheet. Figure 3 shows the hidden worksheet with its named ranges. Storing information in this way is a common technique in Excel programming, and its principal benefit is that by adding our change settings here, the overall solution can adapt to changing needs.

Figure 3. Use a hidden worksheet to hold settings for the Web query

To make it easier to gain access to the defined names in the workbook, the nmDefinedNames variable gains a reference to the Names collection in this way:

Set nmDefinedNames = ActiveWorkbook.Names

The target URL is comprised of the name of a Web server, retrieved from a named range, the name of a Web site, taken from user selection in a combo box embedded in the user's worksheet, and the name of a Web page, also taken from a named range on the hidden worksheet.

The next thing to do is to load the QueryTable definition with a list of parameters in a named/value pair format. This is done by looking at another named range on the hidden worksheet. This named range, called Parameters, can have no rows or many rows containing the names of parameters to pass to the Web page. Because the number and names of parameters can change, loop through the collection of rows in the range to get the names of parameters. Each parameter is listed as text, not as its own named range, and all of the rows in the list are part of the Parameters range. These text names are matched by actual named cells back on the user's worksheet. For example, the Web page expects a BeginDate parameter. We also find a named range with the same name. By separating them in this way, we can distinguish named ranges that refer to parameters for the Web query and those that serve other purposes in the solution. Each of the names and their corresponding values in the user's worksheet are placed in a concatenated string that becomes the value of the PostText property setting of the QueryTable object.

Set rngQueryParams = nmDefinedNames.Item("Parameters").RefersToRange
  For i = 1 To rngQueryParams.Rows.Count
    strParamName = rngQueryParams.Rows(i)
    If i = 1 Then
      strPost = strParamName & 
        nmDefinedNames. Item(strParamName).RefersToRange.Text
    Else
      strPost = strPost & "&" & strParamName & 
        nmDefinedNames. Item(strParamName).RefersToRange.Text
    End If
  Next i

With the basic information provided for the QueryTable definition, you can apply further settings. Because we are going to configure multiple properties of the same object, it is best to place the settings in a With . . . End With block to avoid repeatedly calling the object.

With qtQuery
    .Name = "ReportData"
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .PostText = strPost
    .AdjustColumnWidth = True
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .WebSingleBlockTextImport = True
    .WebDisableDateRecognition = False
    .Refresh BackgroundQuery: = False
    If .FetchedRowOverflow = True Then
      Err.Raise vbObjectError + 1001, "QueryTable", "Too many rows"
    End If
  End With

The properties specified in this example are explained earlier in this article. The PostText property is set using the string that was assembled with the parameter names and values from the worksheet. In addition, the FetchedRowOverflow property is used. If the results are too large, a custom error is raised so that the problem can be properly handled.

Now that the mechanics of retrieving the data are in place, you must place the data in the spreadsheet. However, the dynamically retrieved data also becomes the source for a chart on the user's worksheet, a chart that is equally dynamic in automatically expanding, contracting, and in general responding to changes in the source data.

Dynamic Chart Data

A chart in Excel uses the data points from other cells as its source, some for the X-axis, and some for the Y-axis, as with a two-dimensional line chart includes as part of this business solution. A dynamic chart allows you to specify named ranges for source data rather than specific cells. Those named ranges refer to cells that contain data, excluding those that do not contain data. Thus, as users add new rows, the array of values from the named range reflects the changes. As users remove rows of data, the array of values reflects this change as well.

To make all of this work, you must use several key Excel functions as shown an explained in the following table.

In our business solution, two named ranges in the spreadsheet refer to cells containing data points for the chart. One, called Chart_X_Values, is used to refer to data points on the X-axis, and the other, Chart_Y_Values, is used for data points on the Y-axis. The definition of these two defined names is as follows:

Because the sample Web page returns two columns, one with dates and the other with sales data for each date, these columns provide data points to the chart. The chart begins using data one row down from the titles of the column names, which are in row 11 and in columns C and D. The OFFSET function sets the starting point at cells C12 and D12 respectively for dates and sales. Then, the COUNT function gathers all rows in the respective columns that have values. The solution arbitrarily sets a maximum of 2007 rows for the data. Because the solution requires that users restrict date ranges in the worksheet, you can adjust these values as needed.

Programmatically, the two named ranges that are configured in the worksheet are set for the chart after you import the data using the QueryTable object. Before doing so, you should name the chart. While you could hard-code the name of the chart, to make the solution more adaptable, you can reference a cell that contains the name to use. The cell can use a dynamically assigned name or a simple value. To set a value, select a target cell that includes a column heading for data returned from the Web query. Then, set a value in the cell with this formula: ="Sales"&TEXT(NOW(),""). By adding this formula, the chart updates each time the sheet recalculates. By using the name here as the name for the chart, the name of the chart dynamically changes according to the value in the cell. The code for setting up the chart is as follows:

    Range("ReportSheet!D11").Select
    ActiveCell.FormulaR1C1 = "=""Sales""&TEXT(NOW(),"""")"
    ActiveSheet.ChartObjects(1).Activate
    With ActiveChart
      .ChartType = xlLine
      'Set dynamic retention values
      .SeriesCollection.NewSeries
      .SeriesCollection(1).XValues = "=ReportTool.xls!Chart_X_Values"
      .SeriesCollection(1).Values = "=ReportTool.xls!Chart_Y_Values"
      .SeriesCollection(1).Name = "=ReportSheet!R11C4"
    End With

This code activates a chart already embedded on the page. It also sets the type of chart and identifies the data points for the X and Y values. Finally, the code specifies a name for the chart.

Final Touches

To make the solution more usable, you can also add some basic error handling. The following is a truncated example of the entire event procedure to retrieve the data and configure the chart, with error handling:

Private Sub cmdGetData_Click()
 On Error GoTo HandleError
  Dim qtQuery As QueryTable
  . . .
ExitFinally:
    Set qtQuery = Nothing
    Set qtsQueries = Nothing
    Set nmDefinedNames = Nothing
    Set rngQueryParams = Nothing
    Exit Sub
HandleError:
  If Err.Number = vbObjectError + 1001 Then
    MsgBox "Your request returned too many results." _
      & "Please refine your search.", vbInformation, "Result Error"
      Err.Clear
  ElseIf Err.Number <> 0 Then
    MsgBox "There was a problem retrieving the data you requested from the following URL:" & vbCrLf _
      & strTargetURL & vbCrLf _
      & "Please contact your systems administrator.", vbInformation, "Data retrieval error"
      Err.Clear
  Else
    GoTo ExitFinally
  End If
End Sub

Another useful measure is to let the user clear the report information so that the user can generate an entirely new report. To do this, you want to introduce another embedded button on the worksheet, cmdClearCriteria. The Click Event procedure for this button clears all of the report data, clears dates from the search criteria, removes any chart settings, and finally removes the QueryTable definition from the worksheet. The following code example demonstrates this:

Private Sub cmdClearCriteria_Click()
  Dim strEnd As String
  Dim lngLastRow As Long
  Dim nmDefinedNames As Names
  Dim rngQueryParams As Range
  Dim strParamName As String
  Dim i As Integer

  lngLastRow = ActiveSheet.Range("D65536").End(xlUp).Row
  strEnd = "C11:D" & CStr(lngLastRow)    
  ActiveSheet.ChartObjects(1).Activate
  ActiveChart.SeriesCollection(1).Delete
  Application.Range(strEnd).ClearContents
  Set nmDefinedNames = ActiveWorkbook.Names
  Set rngQueryParams = nmDefinedNames.Item("Parameters").RefersToRange
  For i = 1 To rngQueryParams.Rows.Count
    strParamName = rngQueryParams.Rows(i)
      nmDefinedNames.Item(strParamName).RefersToRange.Value = ""
  Next i
  For i = 1 To ActiveSheet.QueryTables.Count
    ActiveSheet.QueryTables(i).Delete
  Next i
End Sub

After the obligatory variable declarations, this code example determines the number of rows that are populated in the worksheet because of the Web query. After determining the last row, the code example activates the chart and deletes the Series instance from the SeriesCollection object. The example then uses the discovery of the last row to clear the contents of all cells containing imported data. After this, it clears the cells where the user specified a date range and then removes the QueryTable definition from the worksheet. This process prepares the worksheet for the next data request, and allows the Web query process to begin anew.

Conclusion

One of the most common requests any Office developer receives is to help in the creation of reports in Excel. To ease the burden, it is wise to find ways of getting data into Excel as efficiently as possible. You can import existing Web pages rich with data or raw XML directly into Excel using its powerful Web query feature. This feature, represented by the programmable QueryTable object, not only can ease the task for users in creating ad hoc reports, but it can also be part of more planned business solution you develop.

This article described the properties, methods, and events of the QueryTable object and then showed how can be used to deliver a more intelligent solution for users. This solution also described how to let the powerful charting capabilities of Excel benefit from the dynamic power that Web queries offer. Besides enabling users re-query and target different data, this type of solution lends itself very well for adaptation to other needs and reports.

One of the limitations of this solution is its ability to consume unformatted XML. A forthcoming companion paper to this one discusses further customizations to allow you to achieve the same functional results demonstrated here with unformatted XML with the target application Excel 2003, part of the new Microsoft Office 2003 System.