Data Source path in Pivot Table changes to absolute on its own

Anonymous
2013-08-19T15:32:36+00:00

Hello.

I have a .XLSX file, that was created long time ago (I don't even know in which Office version, but definitely not 2013), and maybe even was a .XLS file at first.

So it's a 4 MB file with 16 Sheets and 8 Pivot Tables.

All of the Pivot Tables use other sheets from the same file as Data Source.

Data Source for some of them look like this: 'Sheet3'!$A:$E

Everything is fine when I save the file, and open it from saved file. 

But as soon as I try to move the file elsewhere, or rename it, or email it - all Data Source paths change to something like this: '\Users\Sergii_Litnevskyi\Desktop\New folder[FileName.xlsx]Sheet3'!$A:$E

And it happens with all Pivot Tables. The problem is that it links to an old file path, where the file does not exist anymore. And it links to an external file, which is not what I want.

If I Save As and select different path and filename - then it works fine. So it's a workaround for renaming and moving files, but not for sending them to other persons.

I've read some threads, and people recommend disabling "Save external link values", but it does not help. It is already turned off in my office, but it keeps acting weird. 

So what I need is: Save the file, close it, rename it, move it to other place, send it over email as attachment. And then I want to have the same Data Source path in my PivotTables as I had before I saved the file. How can I do it?

My Office version: Microsoft Excel 2013 (15.0.4454.1503) MSO (15.0.4517.1005) 32-bit

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

45 answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2016-05-05T18:02:35+00:00

    Hi. Still no solution that works for everyone, it seems. And zero interest from Microsoft in knowing about this serious bug.

    I'm quite new to Excel, and only just started using PivotTables, so to run into this problem so soon is rather disappointing. Some more details...

    1. I'm using Excel 2016 (365) without Power Pivot add-in, in Windows 10.
    2. My worksheet has about a dozen pivot tables, all with the same data source, namely a data table located in the same file, called "Table1". There are no external data sources.
    3. When I save my workbook (e.g."Survey v0.4.xlsx") and re-open it, I find that the pivot tables have had their data source changed from "Table1" to "'Survey v0.4.xlsx'!Table1" (double quotes added by me).
    4. When I subsequently save the file under another name (e.g. as "Survey v0.5.xlsx") and re-open it, I get some warning messages associated with the fact that I am now using an external data source. And changes to the data in Table1 of the current file will no longer be reflected in the pivot tables (when refreshed), because they're getting their data from the old file.
    5. I've tried various suggested workarounds, without success. I can restore the source to "Table1" by manually editing out the file reference, or using a macro. But the next time I save the file the problem recurs.
    6. Some people seem to have the whole file path included in the source reference (in square brackets), so they can't even move the file to another folder while keeping the same file name. (I think this may be because their data is in a simple range rather than a table.) Fortunately for me, I don't have that problem. However, I'm making this workbook for other people, and I don't want to have to tell them that they mustn't save the file under another name.

    There seems to be something pseudo-random about the bug, so that for some people the problem goes away when they make a certain change, but it may come back later, while other people get no benefit from the same change. Unfortunately this causes a lot of frustration as people keep reporting they've found a solution, but it doesn't work for other people.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2016-07-12T20:39:27+00:00

    There is a fix at the link.  For each file you have to:

    "Step 1:

    Open the file in excel, uncheck the the option from Excel Options-Trust Center-Privacy Options: “Remove personal information from file properties on save”"

    Next thing is to fix all data sources in a file once. Use whatever way you like.

    That remark was posted on this forum as well. I wonder now why this was a problem for so long.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2016-07-14T15:31:26+00:00

    There is a fix at the link.  For each file you have to:

    "Step 1:

    Open the file in excel, uncheck the the option from Excel Options-Trust Center-Privacy Options: “Remove personal information from file properties on save”"

    Next thing is to fix all data sources in a file once. Use whatever way you like.

    That remark was posted on this forum as well. I wonder now why this was a problem for so long.

    Rafał.Ś

    My workbooks had that unchecked as that's my default.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2016-07-15T17:05:45+00:00

    Can I see one of your files?

    Was this answer helpful?

    0 comments
  5. Anonymous
    2016-07-15T19:56:23+00:00

    Rafał.Ś

    I've been using Excel 2010 since first reporting this problem on July 30th of 2015 in this thread. As I said then I couldn't find the source of the problem and the problem went away when I had Microsoft remote in. At that time I was using Windows 7.

    Since upgrading to Windows 10 I haven't seen the problem in Excel 2013 the few times I checked. I asked if this was the case for other users in this thread on January 27, 2016 but got no response. I think the problem may also relate to the OS being used.

    When I read your original post, the link you supplied ended with a VBA code fix. I didn't see the part you are now referencing which I'm inserting here.

    __________________________________________________________________________________________________

    "Hi,

    I developed a solution, which can fix this annoying problem, but it involves editing the excel archive. More details on this article I wrote for all users with this problem: http://www.excel-first.com/cannot-open-pivot-table-source-file/

    Important: try this solution on a copy of your file, if you make a mistake, your workbook may become irreversibly corrupted.

    Step 1:

    Open the file in excel, uncheck the the option from Excel Options-Trust Center-Privacy Options: “Remove personal information from file properties on save”

    Step 2:

    The key is in the excel archive: if you right click the excel file, and open it with an archiver, this is our guilty folder:

    xl\pivotCache\_rels , the problem is related to pivot cache relationships…

    Inside this folder, there should be at least 1 file, named: pivotCacheDefinition1.xml.rels

    If there are multiple caches, the rest of the files will be: pivotCacheDefinition2.xml.rels, pivotCacheDefinition3.xml.rels and so on.

    From the pivotCacheDefinition1.xml.rels file, simply delete the following part, but ONLY that, otherwise excel will not be able to open it, it will become corrupted:

    Type=”http://schemas.microsoft.com/office/2006/relationships/xlExternalLinkPath/xlPathMissing” Target=”New%20Microsoft%20Excel%20Worksheet.xlsx” TargetMode=”External”/><Relationship Id="rId1"

    Instead of Target=”New%20Microsoft%20Excel%20Worksheet.xlsx” you will see your file name.

    Step 3:

    open the file with excel, SAVE the file, and CLOSE it.

    The problem is gone, you will be able to save the file with another name, the reference will be relative always.

    Regards,

    Catalin Bombea "

    __________________________________________________________________________________________________

    There are no votes for this file so it doesn't stand out as a potential fix.

    I also don't agree that editing the pivotCacheDefinition1.xml.res is very robust based on the warning of complete loss of the file is done wrong. If this is really a fix, Microsoft should fix the pivotCacheDefinition1.xml.rels file making process.

    Based on some quick current testing in Window 10 they may have fixed the problem. If they have, they should make note of the fact and what versions of Windows it applies to.

    Just my perspective as a user that relies on Excel to calculate correctly.

    Was this answer helpful?

    0 comments