Applies to
Microsoft Excel 2002

Web queries can assist you in inserting refreshable data from the Web into your worksheets. Web queries were available in earlier versions of Microsoft Excel, but have been greatly improved. You can now navigate to any Web page while setting up a Web query, the same way as you would navigate to that page in your browser. Once you are at the page, you can select one or more tables by clicking the icon images automatically added to the page by Excel.

Learn the basics of Web queries

A great way to learn the basics of Web queries is to look at one of the sample queries included with Excel.

Using a sample Web query

The MSN® Money Central™ Investor Stock Quotes Web query included with Excel lets you insert the latest stock quotes into your spreadsheets. Just use the Import Data command (Data menu, Import External Data) and select MSN MoneyCentral Stock Quotes as your data source. For the parameter value, use the symbol of any stock you're interested in. (For example, to get Microsoft stock quotes, you would type MSFT.) After you create the query, you can refresh its value at any time to get a new quote.

Creating your own Web query

Now that you have an idea of how Web queries work, you can easily create your own Web queries. However, it is a good idea to have in mind the Web page from which you want to get data and the address of that page, before you begin creating the Web query.

Ways to create a Web query in Excel

There are several different ways to create a Web query in Excel.

Using the Import Data command

The traditional method of creating a web query uses the New Web Query menu command found on the Data menu under Import External Data. If you want to insert one of the sample Web queries that comes with Excel, or if you want to insert one of your own previously saved Web queries, you can use the Import Data command (Data menu, Import External Data).

Using Copy and Paste

Another method of creating a Web query is to start with the Web page open in Microsoft Internet Explorer (version 4.0 or higher). You can then copy the tabular data that you want to import, and paste it into your Excel workbook. When you paste the data in Excel, use the Paste Options button to select the Create Refreshable Web Query option.

For more information on the Paste Options button and other Smart Tags, see Complete Tasks Quickly with Smart Tags in Office XP.

Within Internet Explorer

Internet Explorer (version 5.0 or higher) includes two ways for you to create a Web query in Excel from within Internet Explorer. These methods are described below.

Using the shortcut menu  You can now export tabular data that you find on a Web page to Excel using the shortcut menu. Right-click on the data and choose Export to Microsoft Excel. This feature allows you to start the process of creating a Web query right from within Internet Explorer.

Using the Edit button  Another way to get tabular data from a Web page into an Excel workbook is to use the Edit button in Internet Explorer. This button includes Excel as a valid editor for all Web pages. When you find data that you would like to export to Excel, click the down arrow next to the Edit button and click Edit with Microsoft Excel.

Updating the data

A Web query can be refreshed manually (External Data toolbar, Refresh Data), or you can have it automatically refresh. Using this feature allows you to make sure that your spreadsheets contain the most up-to-date and accurate information.

Changing a Web query

You can change the type of data or formatting that your query returns by editing the query. Click a cell within the queried data and then click Edit Query on the External Data toolbar.

Using Web query with XML sources

Excel supports both reading and writing of XML. Complimenting this feature is the ability to create Web queries to XML files. You can create a Web query to any XML, but if you use the XML Spreadsheet schema, then you can preserve rich spreadsheet concepts like formulas. When you use a Web query to get data from an XML file, you need to select Full HTML formatting in the Web Query Options.

Note  When creating a Web query to an XML file, Excel uses the first processing instruction available, if there is one. If no processing instructions are included, Excel determines whether the proper schema is XML Spreadsheet, MSPersist, or neither. Excel will process the file according to the appropriate schema, or if the file is not in either of those schemas, Excel will process the files using generic XML Flattening code. Excel saves XML files in its own Spreadsheet Schema, and this is the only available format for saving XML in Excel.

Using Web query redirections

When you create a Web query to a specific table on a Web page, Excel looks for the presence of a custom HTML tag called o:WebQuerySourceHRef. If this attribute is present, it means that the author of the Web page understands that people will want to create Web queries to that page and has designed a special version of the data table that is accessed via another Web address. The advantage is that the users of the Web query think they are connecting to nicely formatted HTML tables, but they are actually getting rich and accurate XML Spreadsheet versions of the data.

An example of a Web query redirection in Microsoft Office XP is lists posted on a Web site based on SharePoint Team Services from Microsoft. If you have set up a SharePoint team Web site and you have created a list on that Web site, you can create a Web query to the list and Excel will use the redirection to connect to the underlying data source.

Finding more information on Web queries

The online Help in Excel includes complete task-based and conceptual help topics about Web queries. For more information on Web queries, see Microsoft Excel Help.

Troubleshooting and Limitations

No formatting imported

The default for a new Web query is to bring in no formatting (just plain text). This is because some HTML formatting will prevent subsequent analysis in Excel. For example, merged cells are a common type of HTML formatting, but merged cells will prevent PivotTable® reports and charts from working correctly. To preserve formatting, choose Rich text formatting only or Full HTML formatting from the Web Query Options dialog box, accessed via the Options button on the Edit Web Query dialog box.

The data is incorrectly displayed as dates

Excel automatically tries to interpret dates when data is imported. For example, 03-06 is converted to March 6th. You can turn off Excel’s automatic date recognition in the Web Query Options dialog box, accessed via the Options button on the Edit Web Query dialog box.

No data is returned from a security-enhanced site (sites where login is required)

Excel will not support getting data from security-enhanced Web sites that do not use Microsoft Windows NT® security. A common example of this would be a bank’s Web site that allows you to view your personal account information.

No data, or the wrong data, is returned

There are several possible reasons why data cannot be successfully imported into Excel:

• Make sure that you are connected to your Intranet or to the Internet when you are creating or refreshing a query.
• Some pages use script to generate or populate tables with data. Excel Web queries do not support these types of Web pages. The Web Query dialog box uses the Windows Web Browsing Component to display the Web page so that you can select the data you want. In this dialog box, any script or data binding occurs seamlessly. However, Excel does not use this component when retrieving the data from the site for initial importing and subsequent refreshes. The only information that Excel has access to is the raw HTML source text, without the benefit of reprocessing any scripts or data binding prior to extracting the data. Unfortunately, if neither you nor your company has control over the Web page, there is no workaround for this limitation. However, if you control the content of the Web page you can use redirection as discussed above.

No icons show up on some tables on frameset pages

When you are viewing, in the Web Query dialog box, a Web page that uses frames, sometimes table selection icons won’t be displayed. Clicking the Refresh button in this case may fix the problem, but often this causes the frameset to navigate back to the main page. To see the table icons, click the Hide Icons button twice to show the table icons.