Part IV: Using Advanced Excel Features
612
Using Web Queries
Excel enables you to pull in data contained in an HTML file by performing a Web query. The data
is transferred to a worksheet, where you can manipulate it any way you like. Web queries are espe-
cially useful for data that is frequently updated, such as stock market quotes.The term “Web Query” is a bit misleading. You can perform a Web Query on a local HTML file, a
file stored on a network server, or a file stored on a Web server on the Internet. To retrieve infor-
mation from a Web server, you must be connected to the Internet. After the information is
retrieved, an Internet connection is not required to work with the information (unless you need to
refresh the query).Note
Performing a Web query doesn’t actually open the HTML file in Excel. Rather, it copies the information from
the HTML file. n
The best part about a Web query is that Excel remembers where the data came from. Therefore,
after you create a Web query, you can refresh the query to pull in the most recent data.To create a Web query- Choose Data ➪ Get External Data ➪ From Web. Excel displays the New Web Query
dialog box, shown in Figure 28.6. This dialog box is actually a resizable dialog box that
functions as a Web browser. - Click links to navigate, or just type the URL of the HTML file in the Address field.
The HTML file can be on the Internet, on a corporate intranet, or on a local or network
drive. Each table in the document is indicated by an arrow in a yellow box. - Click an arrow to select the table or tables you want to import.
- You can also control how the imported data is formatted. In the New Web Query
dialog box, click Options (upper right) to display the Web Query Options dialog
box. Select the desired formatting and then click OK to return to the New Web
Query dialog box. - When you’re ready to retrieve the information, click Import.
- In the Import Data dialog box that opens, choose where you want to place the data.
The information on the Web page is retrieved and placed on your worksheet.
After you create your Web query, you have some options. Right-click any cell in the data range
and choose Data Range Properties from the shortcut menu. Excel displays the External Data Range
Properties dialog box, shown in Figure 28.7. These settings control when the data is refreshed,
how it is formatted, and what happens if the amount of data changes when the query is refreshed.To force a refresh at any time, right-click any cell in the data range and choose Refresh. The data in
your worksheet is replaced by the latest of content of the Web page.