XL2000: Cannot Use Web Data Source as PivotTable Data (211912)


The information in this article applies to:

  • Microsoft Excel 2000
This article was previously published under Q211912

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 and PivotChart Wizard.

RESOLUTION

To create a PivotTable that is based on data in a 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 the following steps to create a PivotTable based on data that is in a document on a Web site.

Step 1: Create a Workbook on the Web

  1. Save and close any open workbooks, and then create a new workbook.
  2. Type the following data in Sheet1:
    A1: NameB1: Amount
    A2: BobB2: 1
    A3: SueB3: 2
    A4: Sue B4: 3
    A5: TomB5: 4

  3. Save the workbook as MySource.xls and move it to your Web site on an HTTP server.
  4. Close MySource.xls.

Step 2: Open the Workbook in a Microsoft Excel Window

  1. In Microsoft Excel 2000, click Open on the File menu.
  2. In the File name box, type the web address of your server and the file name you wish to open, for example: http://myserver/MySource.xls.

Step 3: Create the PivotTable

  1. With MySource.xls open in the Microsoft Excel window, create a new workbook.
  2. On the Data menu, click PivotTable and PivotChart Report.
  3. In the PivotTable and PivotChart Wizard - Step 1 of 3 dialog box, click Microsoft Excel list or database, click PivotTable, and then click Next.
  4. In the PivotTable and PivotChart Wizard - Step 2 of 3 dialog box, type [MySource.xls]sheet1!A1:B5, and then click Finish. A PivotTable is created in Sheet1 of the new workbook.

  5. Drag Name from the PivotTable toolbar to the Row Field drop area. Drag Amount from the PivotTable toolbar to the Data Field drop area.
To duplicate the problem, close MySource.xls, and attempt to update the PivotTable: the error message will appear.

REFERENCES

For more information about creating PivotTables, click Microsoft Excel Help on the Help menu, type About creating aPivotTable report from a Microsoft Excel list or database in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:10/8/2003
Keywords:kbprb KB211912
©2003 Microsoft Corporation. All rights reserved.