Microsoft Office Tutorials and References
In Depth Information
Opening Web Pages as Workbooks
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
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 infor
mation, 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 sep
arate 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.