XL97: Cannot Use Web Data Source as PivotTable Data

ID: Q163904

The information in this article applies to:

SYMPTOMS

When you update a PivotTable, you may receive the following error message 

   Cannot open PivotTable source file '<file name>'.
where <file name> is the name of the file that contains the source data.

CAUSE

This problem may occur if you create a PivotTable from data that is in a workbook that is stored on an Hypertext Transfer Protocol (HTTP) World Wide Web site.

NOTE: This problem also occurs if you use a Uniform Resource Locator (URL) in the Range box in step 2 of the PivotTable Wizard.

RESOLUTION

To create a PivotTable that is based on data in file on a Web site, first save the file to your local hard disk, or to a network drive, and then create the PivotTable from the data in this saved file.

MORE INFORMATION

When you open a Microsoft Excel document that is stored on a World Wide Web site, you can either open the document in your Web browser or in a separate window. If the document is opened in a separate window, use the data in the workbook when you create a PivotTable in another workbook.

NOTE: After you close this source workbook, you cannot update the PivotTable.

Use following steps to create a PivotTable based on data that is in a document on a Web site.

Creating the Workbook on the Web

To create a sample workbook, use the following steps:

1. Save and close any open workbooks, and then create a new workbook.

2. Type the following data in Sheet1: 

   A1: Name   B1: Amount
   A2: Bob    B2: 1
   A3: Sue    B3: 2
   A4: Sue    B4: 3
   A5: Tom    B5: 4
3. Save the workbook as MySource.xls and move it to your Web site on an 
   HTTP server.
4. Close MySource.xls.

Opening the Workbook

To configure Microsoft Internet Explorer so that the workbook is opened in a Microsoft Excel window, use the following steps:

1. In Microsoft Internet Explorer, click Options on the View menu.

2. Click the Programs tab and click File Types.

3. In the File Types dialog box, scroll down the list of registered file 

   types, click Microsoft Excel Worksheet, and then click Edit.
4. In the Edit File Type dialog box, clear the "Open web documents in 
   place" check box, and then click OK.
5. In the File Types dialog box, click OK.

6. In the Options dialog box, click OK.

7. Type the URL for MySource.xls in the Address box in Microsoft Internet 

   Explorer.

Creating the PivotTable

To create the PivotTable, use the following steps:

1. With MySource.xls open in the Microsoft Excel window, create a new 

   workbook.
2. On the Data menu, click PivotTable Report.

3. In the "PivotTable Wizard - Step 1 of 4" dialog box, click "Microsoft 

   Excel list or database," and then click Next.
4. In the "PivotTable Wizard - Step 2 of 4" dialog box, click MySource.xls 
   on the Window menu, select A1:B5 in Sheet1, and then click Next.
5. In the "PivotTable Wizard - Step 3 of 4" dialog box, drag Name into 
   the Row field, drag Amount into the Data field, and then click Finish.
A PivotTable is created in Sheet1 of the new workbook.

NOTE: Then you close MySource.xls, and then update the PivotTable, the error message appears.

REFERENCES

For more information about creating PivotTables, click the Index tab in Microsoft Excel Help, type the following text 

   pivottables, creating
and then double-click the selected text to go to the "About creating a PivotTable from a Microsoft Excel list or database" topic.

Additional query words: 97 XL97 pivot table 

Version           : WINDOWS:97
Platform          : WINDOWS

Last Reviewed: October 31, 1998