With Microsoft Excel 2002, you can reach out to the Web and include data you find there in your spreadsheets, without having to understand how the page was created, or how to create a query file. You select only the data you want, and once imported the data can be refreshed with a single click of a button, automatically whenever the workbook opens, or at a specific time interval. For example, you can use a Web query to select data from a financial Web site to track stock market values or an internal Web site that lists sales statistics.
To create a Web query, you use the Import External Data | New Web Query command (Data menu) in Excel. When you choose this option, a dialog box opens with your Internet Explorer home page displayed. You can select areas of the page to import, or switch to another page to import data.
With Excel 2002 and Internet Explorer 4.0 or later, you can also create a Web query directly from a Web page. Menu items have been added to Internet Explorer (Edit in Microsoft Excel on the Internet Explorer toolbar and Export to Microsoft Excel on the right-click context menu) to allow users to export Web page content or an entire Web page to Excel. When you choose these commands, Excel opens, creates a new blank workbook, and automatically imports the data you clicked.
Changing the default home page for Web queries
You can specify a different Web page to use as the default page for Web queries. For example, if your Marketing team refers to a specific page on your intranet for their statistics, you can set the home page for Web queries to their statistics page. This step saves them the effort of navigating to the page each time they want to create a new query. You can change the default home page for Web queries either by setting a system policy or by creating a new registry key.
To specify a default home page, type the URL to the page in the Web Query dialog home page box under the following system policy:
\Microsoft Office\Shared paths\Web Query dialog home page
For more information about using system policies to control user options, see Using System Policies in the Microsoft Office XP Resource Kit
To specify a default home page in the Windows registry, you add the following registry entry as a String type, and type the URL to the home page as the value:
Disabling the Web queries commands
You can disable the New Web Query command (Data menu) in Excel if you do not want users to have access to this feature. To disable the command, you use the following system policy:
\Microsoft Excel 2002\Disable items in user interface\Custom\Disable command bar buttons and menu items
Enter the command bar IDs to disable the New Web Query command and the New Web Query button. For more information about using system policies and locating the command bar ID for a command, see Working with Difficult Policies in the Office XP Resource Kit.
Disabling Export to Microsoft Excel in Internet Explorer
If you do not want users to be able to start Web queries from Internet Explorer, you must disable the menu command from the Windows registry. To do so, in the registry editor, delete the following registry key:
HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\MenuExt\Export to Microsoft Excel
Note The Custom Installation Wizard for Microsoft Office XP gives you the ability to delete registry keys during installation. If you want to remove the Export to Microsoft Excel command during Office XP installation, you can do so by removing the registry key in the Custom Installation Wizard. For more information about using the Custom Installation Wizard, see Customizing User-defined Settings in the Office XP Resource Kit.
Redirecting Web Queries
With Microsoft Excel 2002, you can create a Web query to a specific HTML page that also retrieves data from another location. For example, if an HTML page contains a table of stock quotes, that table data can be used in a Web query, even if the data is being pulled from another data source. Or, if you are pointing to a table on a SharePoint™ team Web site that summarizes project status, you can create a Web query to pull data in from the master status list.
This redirection has two primary advantages. First, the HTML Web page you are importing can be formatted for viewing, even though the data is optimized for analysis (for example, in XML format). Second, the HTML view of the data can be broken up into pages of data (for example, 20 results at a time) but the Web query can be redirected to the full set of data.
This data redirection, however, is potentially problematic. Suppose a user creates a Web query to a summary page, but the Web query does not know where the real data is coming from. In this case, the data might not be able to be updated correctly for the query.
In Excel 2002, a new attribute has been added to use with the TABLE, PRE, XMP, LISTING, or PLAINTEXT tags that identifies where the underlying data is stored. Web page authors can use this redirection attribute, o:WebQuerySourceHRef, to list the data stream's URL, so that Web queries can link to the underlying source data. The data stream can be any supported Web query data type, such as HTML, Text, XML, or Exchange 2000. When this attribute is used, you do not have to worry about getting the correct content when you update — the Web query knows where the data source is and can pull the update directly from the source.
Creating Web query redirections in HTML
Lists created in Web sites based on SharePoint Team Services take advantage of this redirection attribute automatically, and Web authors can now include this attribute in Web sites that they create. Web authors include the Excel namespace declaration in the opening HTML tag, and then add the o:WebQuerySourceHRef attribute to the opening TABLE tag. For example, the opening HTML tag would look like the following:
And the opening TABLE tag would look like the following, where the value is a URL that returns a data stream that is valid for Web queries (such as HTML, XML, Text, or Exchange data):
<TABLE ... o:WebQuerySourceHRef="http://...>
Disabling Web query redirections
Users can disable Web query redirections themselves by using an option in the Web Query Options dialog box.
To disable Web query redirections in the Web Query Options dialog box
- In Excel, on the External Data toolbar, click Edit Query.
- In the Edit Query dialog box, click Options.
- Under Other Import settings, select Disable Web query redirections.
- Under Settings, click Web Options.