none
Pivot table data source keeps referring to old file name RRS feed

  • Question

  • 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 ?  

    Tuesday, August 12, 2014 7:55 AM

Answers

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

    Wednesday, August 13, 2014 3:15 AM
    Moderator

All replies

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

    Wednesday, August 13, 2014 3:15 AM
    Moderator
  • 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

    Thursday, May 28, 2015 8:22 PM
  • MS Office User

    Same scenario and problem.  Just went from Office 10 to 16.    Did you figure out a fix?

    Saturday, May 14, 2016 2:06 PM
  • This thread has more details and suggestions:

    https://social.technet.microsoft.com/Forums/office/en-US/43bf5110-dfad-40e5-a71c-e9736da6fbc2/data-source-path-in-pivot-table-changes-to-absolute-on-its-own

    What helped me: File -> "Inspect Workbook" section -> "Allow this information to be saved in your file".

    Friday, November 11, 2016 8:12 PM
  • Hi both,

    I have exactly the same problem, however the fix outlined by George does not work for me.

    I am using MS Excel 365 on a PC. The Excel file is saved to and opened from OneDrive. I saved an earlier version of the file under a new file name. Now, wen trying to REFRESH the new Excel file the above error message appears, mentioning the old file's filename...

    Please note: the data table in my case is already in the same file as the pivot table. When I look for any connection to the earlier (old) file in my new Excel file, NO CONNECTIONS are found. I do not think it would make sense to create a connection as stated above within one and the same file..wouldn't it?

    Thanks for any support!

    Best, Ralph

    Monday, May 21, 2018 1:26 AM