Different Ways of Using Web Queries in Microsoft Office Excel 2003

  • Article

 

Frank Rice
Microsoft Corporation

February 2004

Applies to:
    Microsoft® Office Excel 2003

Summary: Learn various ways to use Web queries to retrieve data from a Web page. This includes using static and dynamic queries as well as the Add and Find methods. Experimenting with these methods allows you to use the data on Web pages in your own applications. (9 printed pages)

Contents

Introduction
About Web Queries
Retrieving Data from URLs
Retrieving Data by Using FINDER Connections
More on Methods to Retrieve Data
Retrieving Data from a Single Table
Conclusion

Introduction

Web pages often contain information that is ideal for analysis in Microsoft® Office Excel. For example, you can analyze stock quotes in Excel by using information that comes directly from a Web page. Depending on your needs, you can retrieve data that is refreshable (that is, you can update the data in Excel with the latest data on the Web page) or you can obtain data from a Web page as static data and use it on a worksheet. To see how this works, this article demonstrates different ways to retrieve data from Web pages.

About Web Queries

You can create or run a Web query to retrieve text or data on a Web page. Web queries are especially useful for retrieving data that is in tables or preformatted areas. (Tables are defined with the HTML [TABLE] tag; pre-formatted areas are often defined with the HTML [PRE] tag.) The retrieved data does not include pictures, and does not include the contents of scripts.

Important   If you retrieve data from a Web site, it is your responsibility to make sure that you use the data in compliance with any applicable terms and conditions governing the use of the data by the owner and/or operator of such a Web site.

A Web query is a feature in Excel that allows you to retrieve data stored on an intranet, the Internet, or the World Wide Web. A Web query can use static parameters, dynamic parameters, or a combination of both. Queries with static parameters send a query without any input by the user; queries with dynamic parameters prompt you for input. Regardless of the type of parameters in the query, the requested information is pulled from an Internet or intranet site, and the results are placed in a worksheet.

Retrieving Data from URLs

To run a Web query programmatically, you can use the Add method of the QueryTables collection. When you use the Add method, it returns a QueryTable object that represents the new query table. The syntax for the Add method is as follows:

<expression>.Add(Connection, Destination, Sql)

where <expression> is required and returns a QueryTable object. The Connection argument is also required and is the data source for the query table. In the context of Web queries, you can use one of two types of Connection strings: universal resource locator (URL) or FINDER. When you specify the URL type, you use the complete URL to the server on which you are performing the query. You can copy the third line from any Web query [.iqy] file and use it as the URL. When you specify the FINDER type, you use the complete path to an existing Web query (.iqy) file.

Note   The Destination is a required argument, and is the upper-left most cell of the QueryTable object. The Sql argument is optional and not used in Web queries.

Using a Static Web Query

The following example uses static parameters; Excel does not prompt you for the values to send to the server. The parameters are contained in the URL. To create this macro, follow these steps:

  1. Create a workbook.

  2. Start the Visual Basic Editor (press ALT+F11).

  3. On the Insert menu, click Module.

  4. In the module, type the following code:

    Sub URL_Static_Query()

   With ActiveSheet.QueryTables.Add(Connection:= _
      "URL;http://quote.money.cnn.com/quote/quote?symbols=msft", _
         Destination:=Range("a1"))

      .BackgroundQuery = True
      .TablesOnlyFromHTML = True
      .Refresh BackgroundQuery:=False
      .SaveData = True
   End With
End Sub

  5. Switch to Sheet1 in the workbook.

  6. To run the URL_Static_Query macro, point to Macro on the Tools menu and then click Macros.

  7. In the Macro dialog box, click the URL_Static_Query macro and then click Run.

    A stock quote for MSFT is returned to Sheet1.

    Figure 1. Stock quote for stock symbol MSFT

Using a Dynamic Web Query

The following example uses dynamic parameters; you are prompted for the values to send to the server. This example uses the GET method because the parameters are contained in the query. To create the macro, follow these steps:

  1. Switch to the Visual Basic Editor and type the following procedure in the module sheet:

    Note   The following example includes an extra line break to facilitate online display. To use this example, remove the extra line break at the end of the third line.

    Sub URL_Get_Query()
   With ActiveSheet.QueryTables.Add(Connection:= _
      "URL;http://quote.money.cnn.com/quote/quote?symbols=[""QUOTE"",
""Enter stock symbols separated by commas.""]", _
         Destination:=Range("a1"))

      .BackgroundQuery = True
      .TablesOnlyFromHTML = True
      .Refresh BackgroundQuery:=False
      .SaveData = True
   End With
End Sub

  2. Switch to Sheet2 in the workbook and run the URL_Get_Query macro. Because this example uses dynamic parameters, you are prompted for the parameter values.

  3. In the Enter Parameter Value dialog box, type in a valid stock symbol. For example, type msft.

  4. Click OK.

    The stock quote for the stock symbol you entered is returned to Sheet2.

Retrieving Data by Using FINDER Connections

Using a Dynamic Query

With the FINDER type of connection, you specify the full path to an existing Web query (.iqy) file. This example uses the GET method because the Web query file, MSN MoneyCentral Investor Currency Rates.iqy, uses the GET method. That is, the parameters and the URL of the server are on the third line in the file.

Note   The .iqy files used in the procedures in this article are located at C:\Program Files\Microsoft Office\OFFICE11\QUERIES in a complete installation of Microsoft Office 2003.

To create the macro, follow these steps:

  1. Switch to the Visual Basic Editor and type the following macro in the module sheet:

    Sub Finder_Get_Query()
   'Edit path to .iqy file, if necessary.
    IQYFile = "C:\Program Files\Microsoft Office\OFFICE11\" & _
       "QUERIES\MSN MoneyCentral Investor Currency Rates.iqy"
    With ActiveSheet.QueryTables.Add(Connection:= _
       "FINDER;" & IQYFile, Destination:=Range("A1"))

       .BackgroundQuery = True
       .TablesOnlyFromHTML = True
       .Refresh BackgroundQuery:=False
       .SaveData = True
    End With
End Sub

  2. Switch to Sheet3 in the workbook and run the Finder_Get_Query macro.

    A list of currency rates are returned to Sheet3.

Using the Dynamic Query

With the FINDER type of connection, specify the full path to an existing Web query (.iqy) file. This example uses the GET method because the Web query file, MSN MoneyCentral Investor Stock Quotes.iqy, uses the GET method. That is, the parameters are on the fourth line, and the URL of the server is on the third line in the file. To create the macro, follow these steps:

  1. Switch to the Visual Basic Editor and type the following procedure in the module sheet:

    Sub Finder_Get_Query()
   ' Edit path to .iqy file, if necessary.      
   IQYFile = "C:\Program Files\Microsoft Office\OFFICE11\" & _
       "QUERIES\MSN MoneyCentral Investor Stock Quotes.iqy"
   With ActiveSheet.QueryTables.Add(Connection:= _
      "FINDER;" & IQYFile, Destination:=Range("A1"))

      .BackgroundQuery = True
      .TablesOnlyFromHTML = True
      .Refresh BackgroundQuery:=False
      .SaveData = True
   End With
End Sub

  2. Switch to Sheet4 and run the Finder_Get_Query macro. Because this example uses dynamic parameters, you are prompted for the parameter values.

  3. In the Enter Parameter Value dialog box, type a valid stock symbols. For example, type msft.

  4. Click OK.

    The stock quote for the stock symbol you entered is returned to a table in Sheet4.

More on Methods to Retrieve Data

The following sections provide more information on different methods for retrieving data from Web pages. As an example, you retrieve exchange rates from x-rates.com.

Figure 2. The currency exchange rates table

Retrieving Data by Using the Find Method

The following procedure opens the entire Web page. The procedure then scans the page for the desired information which, in this case, is the Canadian/United States exchange rate.

  1. Type the following macro in the module sheet:

    Sub OpenUSDRatesPage()
   Dim objBK As Workbook
   Dim objRng As Range

   'Open the page as a workbook.
   Set objBK = Workbooks.Open("http://www.x-rates.com/tables/USD.HTML")

   'Find the Canadian Dollar cell.
   Set objRng = objBK.Worksheets(1).Cells.Find("Canadian Dollar")

   'Retrieve the exchange rate.
   MsgBox "The CAD/USD exchange rate is " & objRng.Offset(-6, -1).Value
End Sub

  2. Switch to Sheet5 and run the OpenUSDRatesPage macro. The following dialog box is displayed:

    Figure 3. The Canadian/US exchange rate dialog box

    **Note   **The greatest disadvantage to this method of retrieving data is that it loads the entire Web page into memory including any graphics that the page may contain. This extraneous information can severely slow the speed of retrieving data.

Retrieving Data from a Single Table

In contrast to the previous method, the following Web query allows you to retrieve data from a single table. In addition, this Web query provides an option to refresh the data each time a user opens the workbook or refreshes it during timed intervals.

The following procedure retrieves a table of exchange rates by executing a Web query:

Sub RatesWebQuery()
   Dim objBK As Workbook
   Dim objQT As QueryTable
   
   Dim strDecimal As String
   Dim strThousand As String
   Dim boolUseSystem As Boolean
   
   'Create a new workbook.
   Set objBK = Workbooks.Add
   
   'Create query table to hold the rates.
   With objBK.Worksheets(1)
      Set objQT = .QueryTables.Add( _
         Connection:="URL;http://www.x-rates.com/tables/USD.HTML", _
         Destination:=.Range("A1"))
   End With
   
   'Set QueryTable properties.
   With objQT
      .Name = "USD"
      
     'Don't recognize dates.
      .WebDisableDateRecognition = True
      
      'Don't refresh query when file opened.
      .RefreshOnFileOpen = False
      
      'Ignore page formatting.
      .WebFormatting = xlWebFormattingNone
      
      'Wait for query to finish before continuing.
      .BackgroundQuery = True
      
      'Select a specific table.
      .WebSelectionType = xlSpecifiedTables
      
      'Import the table containing the exchange rates.
      .WebTables = "15"
      
      'Save the query with workbook.
      .SaveData = True
      
      'Adjust columns to fit the data.
      .AdjustColumnWidth = True
    End With
    
    With Application
        'Store number format settings.
        strDecimal = .DecimalSeparator
        strThousand = .ThousandsSeparator
        boolUseSystem = .UseSystemSeparators
        
        'Set XL separators to match the Web site.
        .DecimalSeparator = "."
        .ThousandsSeparator = ","
        .UseSystemSeparators = True
        
        'Ingore any errors.
        On Error Resume Next
        
        'Execute query and wait for it to finish.
        objQT.Refresh BackgroundQuery:=False
        
        'Reset number format settings.
        .DecimalSeparator = strDecimal
        .ThousandsSeparator = strThousand
        .UseSystemSeparators = boolUserSystem
    End With
      
End Sub

Excel retrieves the following table.

Figure 4. An exchange rate table

Notice the use of the properties Application.DecimalSeparator, Application.ThousandsSeparator, and Application.UseSystemSeparators in the procedure. In versions of Excel earlier than Excel 2002, there was a problem with the way Excel recognized numbers on a page. In many European countries, the period denotes thousands resulting in exchange rates that were much too high when Excel interpreted that data. Excel used the settings in the Microsoft Windows® Regional Settings when attempting to recognize numbers on a page.

In Excel 2002 and later, three properties were added to the Application object to temporarily override the settings used when recognizing numbers:

  • Application.DecimalSeparator. Character used for the decimal separator.
  • Application.ThousandsSeparator. Character used for the thousands separator.
  • Application.UseSystemSeparators. Character that specifies whether to use the separator from Windows or Excel.

Conclusion

This article demonstrates different ways to retrieve data from a Web page or file. Using the Find method allows you to retrieve an entire page of data. Using the Add method allows you to use a Web query to retrieve specific data from a Web page. Additionally, the article discusses various properties for handling numeric data that is retrieved with a Web query. Using the proper method or property to fit your needs can help make your applications more efficient.