Excel and your Web browser can work together to gather information from a source on the Web. Microsoft Excel Web Query files are simple text documents that include a pointer to a Web page, plus a few lines that define parameters for the query. When you run a Web Query, Excel opens a connection to the Internet, connects with the specified Web page, executes the query, and returns the data to Excel.
Writing a Web Query from scratch takes specialized development skills, but you can pull in a surprising amount of useful data using sample Web Query files that come with Excel. For example, you can connect with Microsoft Investor to download current prices for stocks and mutual funds. To open any of the three sample Web queries, choose Data, Import External Data, Import Data.
You don't have to write a single line of code to bring data from the Web into an Excel worksheet, however. If the original data is contained in a table on any Web page, you can copy the table's data to the worksheet and specify that you want to transform it into a refreshable Web query. Follow these steps:
Click to select the cell where you want the table data to appear.
In the New Web Query dialog box (see Figure 25.24), enter or paste the URL of the page that contains the data that you want to add to the worksheet. Click the Go button to load that page.
By default, Excel pulls your data in with no formatting. If you want to pick up text formatting from the Web page, click the Options button. In the Web Query Options dialog box (see Figure 25.25), check the Rich Text Formatting Only option. Use the Full HTML Formatting option if you want all table properties to be preserved.
Click OK to add the Web data to your worksheet. (Click the Save Query button first if you want to save the query settings as a separate file that you can open in another worksheet.)
Tip from
If this process seems too cumbersome, you can speed things up by using the Clipboard. From an Internet Explorer window, select the data that you want to add to your worksheet, right-click, and choose Copy. Paste the data into your worksheet, and click the Paste Options Smart Tag. Click the Create Refreshable Web Query menu option to convert the pasted data to a live link. This option is available only if you have selected a full table.
18.191.253.62