locked
Problems refreshing pivot tables in Excel 2013 RRS feed

  • Question

  • I have a file with pivot tables connected to a table in a separate sheet (in the same file). The table is connected to a datasource (an access database) and I save the file with a new name and update the data in the sheet. When I try to refresh the pivot tables to reflect the new data they have the old file name in the datasource causing them to not being able to update (i.e. 'File name 2015 3 - RR.xlsb'!TableName instead of just TableName).

    In Excel 2010 it worked fine resaving the file with a new name and refreshing the pivots. how do I fix this problem? I've been trying to update the datasource by removing the file name but that's not working because it's still asking for the old file.

    /A

    Tuesday, March 31, 2015 1:21 PM

Answers

  • Hi,

    As far as I know, this issue might be caused by the Excel file contain the personal information(data source), such as Access database name, path. etc... After we closed the file, Excel 2013 generated the full name and path of the data source and stored in the file. If we renamed the file, Excel could not find the original file name. Thus, please try the 2 workarounds:

    ===

    One:

    Create a copy of file as test file =>Change the file edition from XLSB to ZIP. =>Double click the Zip=>Go to test one1.zip\xl\pivotCache\pivotCacheDefinition1.xml => Open pivotCacheDefinition1.xml in Notepad.=> Find <worksheetSource name="Access database name.accdb" r:id="rId2"/> => change rId2 to rid1. => Change Zip to XLSB

    Two:

    Use XLS format.

    ===

    Hope it's helpful.

    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.

    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    • Proposed as answer by George123345 Tuesday, April 7, 2015 8:56 AM
    • Marked as answer by George123345 Wednesday, April 8, 2015 8:17 AM
    Wednesday, April 1, 2015 7:33 AM

All replies

  • Hi Bylla77,
     
    This is a known problem in 2013 I seem to recall.
     
    Perhaps you can connect the pivot table to the external source directly?
     
     

    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    Tuesday, March 31, 2015 1:29 PM
  • Hi,

    Thanks for the suggestion. I will try that.

    Do you have any link to the bug information?

    /A

    Wednesday, April 1, 2015 6:43 AM
  • Hi,

    As far as I know, this issue might be caused by the Excel file contain the personal information(data source), such as Access database name, path. etc... After we closed the file, Excel 2013 generated the full name and path of the data source and stored in the file. If we renamed the file, Excel could not find the original file name. Thus, please try the 2 workarounds:

    ===

    One:

    Create a copy of file as test file =>Change the file edition from XLSB to ZIP. =>Double click the Zip=>Go to test one1.zip\xl\pivotCache\pivotCacheDefinition1.xml => Open pivotCacheDefinition1.xml in Notepad.=> Find <worksheetSource name="Access database name.accdb" r:id="rId2"/> => change rId2 to rid1. => Change Zip to XLSB

    Two:

    Use XLS format.

    ===

    Hope it's helpful.

    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.

    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    • Proposed as answer by George123345 Tuesday, April 7, 2015 8:56 AM
    • Marked as answer by George123345 Wednesday, April 8, 2015 8:17 AM
    Wednesday, April 1, 2015 7:33 AM