Different Ways of Using Web Queries in Microsoft Office Excel 2003
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)
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.
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.
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)
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:
- Create a workbook.
- Start the Visual Basic Editor (press ALT+F11).
- On the Insert menu, click Module.
- 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
- Switch to Sheet1 in the workbook.
- To run the URL_Static_Query macro, point to Macro on the Tools menu and then click Macros.
- 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:
- 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
- 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.
- In the Enter Parameter Value dialog box, type in a valid stock symbol. For example, type msft.
- Click OK.
The stock quote for the stock symbol you entered is returned to Sheet2.
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:
- 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
- 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:
- 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
- Switch to Sheet4 and run the Finder_Get_Query macro. Because this example uses dynamic parameters, you are prompted for the parameter values.
- In the Enter Parameter Value dialog box, type a valid stock symbols. For example, type msft.
- Click OK.
The stock quote for the stock symbol you entered is returned to a table in Sheet4.
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.
- 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
- 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.
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.
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.