Creating and Using Web Queries

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:

  1. Click to select the cell where you want the table data to appear.

  2. Choose Data, Import External Data, New Web Query.

  3. 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.


    Figure 25.24.


  4. The New Web Query dialog box identifies each table in the page with a small arrow. Click the arrow next to the table (or tables) that you want to add. The box turns to a green check mark after you've selected it.

  5. 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.


    Figure 25.25.


  6. Click the Import button to open the Import Data dialog box. The cell that you selected in Step 1 is highlighted. Change this location, if necessary, and click the Properties button if you want to adjust the refresh times for the live data.

  7. 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.)

  8. Repeat Steps 1–7 to add data from another Web page to your 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.


..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.191.253.62