O'Reilly Book Excerpts: Excel 2003 Programming: A Developer's Notebook
Performing Web Queries in Excel 2003by Jeff Webb
Editor's note: It may not seem as though much has changed in Excel 2003, but if you're an Excel VBA programmer, you will want to know about some new programming features. Excel 2003 Programming: A Developer's Notebook will get you up to speed quickly on working with lists and XML data, securing Excel applications, using Visual Studio Tools for Office, collecting data with Infopath, and more--in the form of hands-on labs that address specific programming problems. This sample lab on performing web queries offers a glimpse at the sort of projects you'll find in the book, which is due for release in late August 2004.
Web queries are a quick way to import data from a web page into a worksheet using a QueryTable object.
How do I do that?
To perform a web query:
- Choose Data --> Import External Data --> New Web Query. Excel displays the New Web Query dialog as shown in Figure 1.
- Type the address of the web page you want to import data from in the Address bar and click Go to navigate to that page. It is usually easiest to find the page you want in your browser, then cut and paste that address into the New Web Query dialog box.
- Excel places small yellow boxes next to the items you can import from the page. Click on the item or items you want to import and Excel changes the yellow box to a green check mark.
- Click the Options button to set how Excel formats imported items. Formatting options are shown in Figure 2.
|Figure 1. Use web queries to import data directly from a web page.|
|Figure 2. Set formatting options for the query.|
- Close the Options dialog box and click Import. Excel displays the Import dialog box as shown
in Figure 3.
Figure 3. Choose the destination for the imported data.
- Click Properties to determine how the query is performed, such as how the
data is refreshed. Figure 4 shows the query property settings.
Figure 4. Use query properties to name the query, set how data is refreshed, and define how cells are inserted.
- Close the Properties dialog and click OK to import the data. Figure 5 shows a real-time stock quote and quote history imported from the Yahoo web site. Yahoo is a good source for this type of web query because it is a free service and doesn't require you to register or sign in.
Figure 5. Using a web query to get stock price data.
Pages: 1, 2