Pivot table data source keeps referring to old file name

Hi,

I am using Excel 2013 on Windows 8

I create a table, and insert a pivot table that use this table as data source

I close the excel and rename the file from "myfile.xlsx" to "newfile.xlsx, and then open it again

When trying to refresh my Pivot Table, I get the following Error:

"We couldn't get the data from 'Table1' in the workbook 'C:\myfile.xlsx' Open this workbook in Excel and try again."

i.e. it is still looking for table using the old file name.

I need to update the file every week, so having to manually go back and change source file name for every pivot is very time consuming and painful for me :-(

Please investigate and advise how to fix this ?  

August 12th, 2014 7:55am

Hi,

Based on my tested in my environment (Window 8.1 & Excel 2013), the error message pop up when a pivot table using an external data source. If you use the table as external data source, pivot table uses OLEDB to connect it, it'll refresh failed after you changed the file name. It's by design.

Then, we may try the two workaround to avoid it:

1) Keep the data source table and pivot table in the same file.

2) Add the table to a connection, steps:

Click Data tab>Connection> Add> Browse more> Choose the file.

Please try to change in new file in property before you refresh the Pivot table.

Regards,

George Zhao
TechNet Community Support

It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

Free Windows Admin Tool Kit Click here and download it now
August 13th, 2014 3:15am

Hi,

Based on my tested in my environment (Window 8.1 & Excel 2013), the error message pop up when a pivot table using an external data source. If you use the table as external data source, pivot table uses OLEDB to connect it, it'll refresh failed after you changed the file name. It's by design.

Then, we may try the two workaround to avoid it:

1) Keep the data source table and pivot table in the same file.

2) Add the table to a connection, steps:

Click Data tab>Connection> Add> Browse more> Choose the file.

Please try to change in new file in property before you refresh the Pivot table.

Regards,

George Zhao
TechNet Community Support

It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

August 13th, 2014 3:15am

I have this same problem, however I am changing the file name by "save as" (not externally in the folder).  I am just changing the file name with the V2, V3, V4 or changing the file name to have a different date extension (20150522 to 20150529)

I just changed from MS Office 2010 to 2013 and this problem started.

I built out a spreadsheet with multiple tabs of pivot tables all referencing the master data tab in the same spreadsheet (any external data is Index-Match function into the spreadsheet and then copy-paste values.  

When I "save as" the document as a new name, the pivot tables all refer back to the old document.  Not sure why this is happening and it is very frustrating and time consuming to update the data ranges.

Is this a MS 2013 bug?  Is there any way to lock down the data reference fields to stay in the document and not try to refer externally?

Thanks

Free Windows Admin Tool Kit Click here and download it now
May 28th, 2015 4:23pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics