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

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.

Can I see one of your files?

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.

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.

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.