|< Day Day Up >|| |
The Excel application has two commonly known sources of data: databases on any given network, and the user. Traditionally, if an item of data wasn't available in a database, the user was required to type it in and maintain it. To enable this, the application had to include a number of sheets and dialog boxes to store the information and provide a mechanism for the data entry.
A typical example of this would be maintaining exchange rate information in a financial model. It's usually the user's responsibility to obtain the latest rates and type them into the model. You can add value to the application by automating the retrieval of up-to-date exchange rate information from one of many Web sites.
The following sections demonstrate different techniques for retrieving information from the Web, using the USD exchange rate available from http://www.fms.treas.gov/intn.html#rates as an example. The Web page should look similar to Figure 25-4.
Figure 25-4: The U.S. Treasury site shows sample data laid out in a table format. This information will be evaluated in the next sections.
The simplest solution is to open the entire Web page as if it were a workbook, and then scan the sheet for the required information, such as the USD/Canadian exchange rate. However, the problem with this approach is that the entire Web page is loaded. The Web page usually includes graphics, banners, and information that aren't required. The additional data will slow down the performance of the speed of data retrieval. Later in this chapter, you'll review using Web Queries to improve performance.
In the meantime, review the following procedure that opens the U.S. Treasury Web site within Excel:
Dim webBk As Workbook
Dim webRng As Range
Set webBk = Workbooks.Open("http://www.fms.treas.gov/intn.html#rates")
Set webRng = webBk.Worksheets(1).Cells.Find("CANADA - DOLLAR")
MsgBox "The USD/Canadian exchange rate is " & webRng.Offset(0, 1).Value
This procedure uses the Open method to open an existing Web page rather than a Web browser. When the Web page appears, it then searches for the specified type of currency and displays the current exchange rate in a message box. This procedure can be used with other Web pages that display information in a table format. Simply enter the appropriate label in the Find criteria. Remember that if you are actively searching the Internet for updated information, you'll require an active Internet connection.
Web Page Frames There might be times when a Web page is opened in Excel, but nothing is there. Don't worry, you did open the page correctly, but Excel can't open Web pages that contain frames.
A frames page allows a single Web page to be divided into sections that each displays a separate Web page. Each of these sections is called a frame. The frames page is the container that hosts the group of frames. Excel can't open a Web page that contains a frames page.
If you know how to work with HTML source code, you can use an HTML editor to copy the information you want, without the frames page code, to a new file. When the new file has been created, you can open it in Excel.
Web Queries were introduced in Excel 97 and have been enhanced with each subsequent version of Excel. They allow you to retrieve a single table of information from a Web page, with options to automatically refresh the data each time the workbook is opened, or at specific intervals.
One of the problems with Web Queries is that Excel uses the thousands and decimal separators specified in the Windows Regional Settings when attempting to recognize numbers in the page. If, for example, an exchange rate Web page is retrieved from one of many European countries, the period is treated as a thousands separator, not a decimal separator, resulting in exchange rates that are many times too large. Therefore, Web Queries couldn't be reliably used in versions prior to Excel 2002 in countries that used non-U.S. format decimal and thousand separators. However, now there's a workaround for this problem.
There are three properties to the Application object to temporarily override the settings used when recognizing numbers. The following properties are available:
Using these properties, you can set Excel's separators to match those on the Web page, perform the query, and then set them back again. If you want to use the Web Query's automatic refreshing options, you have to set these separators in the BeforeRefresh event and set them back in the AfterRefresh event. To capture these events, class modules need to be set up.
The following procedure demonstrates how to retrieve the table of exchange rates using a Web Query created within the procedure.
The RetrieveWebQueryData procedure imports the table of exchange rates into the Excel workbook. If you don't see the Exchange Rates in the workbook, verify which table you need to import. The sidebar 'That Was Table Number 2?', on p. 531, explains this scenario.
The .WebTables = 2 line in the preceding example tells Excel that you want the third table on the page. Literally, this is the third occurrence of a <TABLE> tag in the source HTML for the page.
With Excel, you have the ability to incorporate data that you locate from the Web into your spreadsheets. It's not necessary to know how the page was created or how to create a query file. You can select the data you want, and once the information is imported into the spreadsheet, you can refresh the data with a single click of the mouse. Alternatively, the data can be refreshed when the workbook is opened, or at specific time intervals.
You can alternatively create a new Web Query using the Import External Data feature within Excel. Follow these steps outlined to review how to navigate through the dialog box.
Click Data, Import External Data, and then New Web Query.
Type the Web page URL in the Address text box.
Click the Go button to show the Web page within the New Web Query dialog box. Notice the arrows that appear on the Web page. The arrows when selected will outline the entire table to be imported into your worksheet.
Select the table to import. When the table is selected, the arrow changes to a check mark as displayed in Figure 25-5.
Figure 25-5: The New Web Query dialog box shows the table of exchange rates ready to be imported into the Excel spreadsheet.
Click the Import button.
Two options are available to place the contents of the Web query. Select Existing Worksheet if you want to display the data in the active worksheet, or select New Worksheet if you want the data to be placed in a new sheet.
There are times when you view a Web page that contains many tables. Web sites that contain many tables can make it difficult to determine the number of the WebTables property. It might be difficult to determine the table number, but there are ways to verify the number.
A quick way to verify the number of a table within the Web site is to record a macro and set up a Web Query. The Web Query should import the table you need to work with in a new sheet to avoid complications. After the Web Query has been created, stop recording the macro.
Review the recorded macro code until you find the WebTables property. The value assigned to the WebTables property will be the table number you were searching for.
There are times when recording macros can assist in finding values and properties that are otherwise tedious to locate. Sometimes when working through new properties and new object models, recording simple macros and reviewing their code will show you properties and methods you didn't know existed. Keep in mind that any recorded macros should be cleaned up, however. There are always additional lines of code that are unnecessary.
Web Queries are an excellent way of retrieving tables of information from Web pages, but they're a little cumbersome if you're only interested in one or two items of information. Another way is to read the page using a hidden instance of Internet Explorer, search within the page for the required information, and then return the result. The following code requires you to click the Tools, References command and select the Microsoft Internet Controls object library:
Dim webIE As SHDocVw.InternetExplorer
Dim strPage As String
Dim lngCanadian As Long
Dim lngDec As Long
Dim lngStart As Long
Dim lngEnd As Long
Dim dblRate As Double
Set webIE = New SHDocVw.InternetExplorer
Do Until webIE.ReadyState = READYSTATE_COMPLETE
strPage = webIE.Document.body.innerHTML
lngCanadian = InStr(1, strPage, "CANADA - DOLLAR")
lngDec = InStr(lngCanadian, strPage, ".")
lngStart = InStrRev(strPage, ">", lngDec) + 1
lngEnd = InStr(lngDec, strPage, "<")
dblRate = Val(Mid$(strPage, lngStart, lngEnd - lngStart))
MsgBox "The USD/Canadian exchange rate is " & dblRate
In determining whether to use a Web Query or parse a Web page, the most appropriate method to use will depend on the precise circumstances and how much data is required. For single items, it's probably easier to search the Web page for the required information. However, for more than a few items it'll be easier to use a Web Query to read the page or table into a workbook and then find the required items on the sheet.
|< Day Day Up >|| |