Using a Web Query to Return Internet Data


Web queries let you grab specific information, such as stock prices, sports scores, or your company's current sales data, from the Internet or an intranet. You can set up queries to prompt you for the data you want (for stock ticker symbols, for example) or to get the same information every time they're executed. You can try Web queries using a set of sample queries that come with Excel 2007.

The Excel graphical interface for creating Web queries lets you build a query by pointing to the data you want. You can refresh the query at any time or at regular intervals, and you can save the query in an IQY file for reuse in other worksheets. You do not need to understand how the target Web page is built to construct a query to it.

Using an Existing Web Query

To run an existing Web query-one of the samples supplied with Excel or one that you or someone else has already set up-click the Data tab, and click Existing Connections. As Figure 23-31 shows, Web queries are identified in the Existing Connections dialog box by a pair of intersecting blue rectangles and a globe. (The three Web queries supplied with Excel 2007 are all from MSN MoneyCentral Investor.)

image from book
Figure 23-31: Web queries are marked by intersecting rectangles and a globe, and Excel 2007 includes three of them to try.

When you double-click a Web query, Excel prompts you to specify a location for the incoming Web data. Depending on how the query was set up, it might also prompt you for parameters. For example, if you double-click the MSN MoneyCentral Investor Stock Quotes query, after you indicate where you want the data to go, you will see the following dialog box, which prompts you to supply one or more stock symbols:

image from book

To supply parameters via the Enter Parameter Value dialog box, simply type in the box. If you prefer, you can point to a worksheet range containing your parameters. If you specify a multicell range, Excel parses the range moving across and then down.

If you use a worksheet range to feed parameters to your Web query, you can also stipulate that the query be refreshed automatically anytime the worksheet range changes. To do this, select both check boxes.

Figure 23-32 shows an example of data returned by one of the Web queries supplied with Excel 2007. Note that this query is set up to return the names of market indexes as hyperlinks. Clicking a hyperlink takes you to a relevant page in the MSN MoneyCentral Investor Web site.

image from book
Figure 23-32: Data returned by this Web query includes hyperlinks to the MSN MoneyCentral Investor site.

Creating Your Own Web Query

Excel provides three easy ways to construct a Web query:

  • Clicking the Data tab and then clicking From Web

  • Copying and pasting information from your Web browser

  • Right-clicking in Microsoft Internet Explorer and clicking Export To Microsoft Excel

Using the From Web Command

To create a Web query using the From Web command, follow these steps:

  1. Click the Data tab, and then click From Web. The New Web Query form that appears (see Figure 23-33) is a specialized Web browser, and your home page appears in its window.

  2. If you know the Uniform Resource Locator (URL) of the Web site you want to query, you can type or paste it into the Address field. (Unfortunately, the New Web Query form doesn't include a Favorites menu.)

  3. Click Go; your Web site appears in the main window, as Figure 23-34 shows.

image from book
Figure 23-33: The New Web Query form is a specialized Web browser.

image from book
Figure 23-34: We've displayed the site we want to query in the New Web Query form.

Yellow boxes with arrows appear along the left edge of the window. Each of these boxes represents a section of the Web site that you can import into Excel. As you rest your pointer on any of these yellow boxes, a thick bounding rectangle indicates the section of the site you will be importing if you select the yellow box. You can select any or all yellow boxes.

Saving the IQY File and Setting Formatting Options After making your selections, you can click Import to transform your selection into a query. But before you do so, you might want to save the query (making it a reusable IQY file) or explore the menu of options. To save the query in its current form, click the Save Query command, directly to the left of the word Options on the toolbar in the New Web Query form. To set options, click Options. Figure 23-35 shows the Web Query Options dialog box.

image from book
Figure 23-35: In addition to offering other features, the Web Query Options dialog box lets you control how much of the Web site's formatting Excel should preserve.

In the Formatting area of the dialog box, select None to import the data as plain text. Select Rich Text Formatting Only to preserve hyperlinks and merged cells in the Web data. Select Full HTML Formatting to retrieve as much as possible of the original Web site's formatting. Figure 23-36 shows a Web site queried with full HTML formatting. Note the inclusion of hyperlinks in the downloaded data.

image from book
Figure 23-36: Because we queried this site using full HTML formatting, the downloaded data includes active hyperlinks and other welcome formatting characteristics.

Copying and Pasting from the Web Browser

The method for creating a Web query just described is fine if you're starting in Excel and you know the address of the target site. But you can also start from the Web browser. Select the data you want, press Ctrl+C to copy it, open a new Excel worksheet, and press Ctrl+V. You'll see a smart tag near the lower-right corner of the pasted data. Open the menu, shown here, and click Create Refreshable Web Query. Your data selection will appear in the New Web Query form.

image from book

Using the Internet Explorer Export To Microsoft Excel Command

If your Web browser is Internet Explorer, you can also create a Web query by right-clicking a Web page and clicking Export To Microsoft Excel. (If you don't see this command, you probably already have something selected in Internet Explorer. Clear your selection, and try again.) The Export To Microsoft Excel command begins by creating a new instance of Excel (it does this to avoid overwriting Excel data you might already be working with). If you right-clicked something that Internet Explorer recognizes as an HTML table, it transfers that table directly onto Sheet1!A1 as a new Web query. If you clicked anywhere other than an HTML table, the command opens the New Web Query form.




Microsoft Office Excel 2007 Inside Out
MicrosoftВ® Office ExcelВ® 2007 Inside Out (Inside Out (Microsoft))
ISBN: 073562321X
EAN: 2147483647
Year: 2004
Pages: 260

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net