Cannot use Web data source as PivotTable data in Excel

When you update a PivotTable, you may receive the following error message where <file name> is the name of the file that contains the source data.

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.

You cannot use a self-referencing PivotTable on a portal if the stored location of the worksheet is in the Temporary Internet Files folder. In this scenario, Excel calls the file by name, but Windows temporarily names it by using a unique alpha-numeric name instead of by using the name of the file. This problem does not occur if the worksheet is hosted in an online datasheet. To make sure that the RAW data is not exposed to possible alteration by an unauthorized source, segment the datasheet from the PivotTable.

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.

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: 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.

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.

To work around this problem, open a self-referencing pivot table from an internet portal. To create a sample datasheet and a pivot table to reference it, follow these steps:

Step 1: Create the datasheet

1. Logon to your Share Point Portal server team Web site where you want to publish the datasheet.
2. Click Create.
3. Under Custom lists, click Custom list in Datasheet view.
4. On the New List page, provide a name for your new datasheet. If you want, add a description. If no one else is going to edit your datasheet, click No so that it is not displayed quick launch bar, and then click Create.
5. The next page displays a blank and editable datasheet. Right-click the Title column, and then click the Edit/Delete column to view the first column's properties.
6. Change the column name to Name, add a description, and then click OK to save the change.
7. Right-click the column again. This time, click Add Column.
8. Name the new column Amount, change the Type to number, and then add a description if you want. Click OK to save the new column and add it to the datasheet.
9. Add names and amounts to your new datasheet.
10. Create an XML-based query for your spreadsheet. To do this, click Export to spreadsheet from the action list to the left of the datasheet.
11. To create the new pivot table from the query, save the *.iqy file to your local computer.

Step 2: Create the pivot table

1. Start Excel.
2. On the the Data menu, click PivotTable and PivotChart Report. The PivotTable and PivotChart Report Wizard appears and prompts you for a data connection. Click External data source, and then click Next.
3. Click Get Data, click Browse, and then locate the *.iqy file that you saved earlier in step 11.
4. The data is inserted into the spreadsheet, and a list of the available columns appears on the right side. To insert those columns in the table, click to select both columns. Notice it automatically tabulates the amounts for each person and provides a total at the bottom.
5. Save the new Excel PivotTable on your local computer.

Microsoft Office Excel 2003

If you have to store the data on a Microsoft Office SharePoint site, you can create a datasheet on the SharePoint Web site. Then, you can export that datasheet to a spreadsheet. During the process in which the spreadsheet is exported, you can choose to create a PivotTable.

For more information about creating PivotTables, click Microsoft Excel Help on the Help menu, type About creating a PivotTable report from a Microsoft Excel list or database in the Office Assistant or the Answer Wizard, and then click Search to view the topic.