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

Rafał.Ś

The link was referenced previously, but it also goes into more detail on the cause. Basically Microsoft stores hidden XML data in the spreadsheet with pivot tables. For some reason an XML property

//schemas.microsoft.com/office/2006/relationships/xlExternalLinkPath/xlPathMissing

Sets some external reference to something even though the spreadsheet has no external references. This seems to cause the spreadsheet to then embed the reference to the original file when it is saved as a different file name or renamed or moved.

The link I supplied goes directly to the description and not to a thread with lots of other stuff as your link did.

I'm going to give Microsoft a call to see if they have seen this and if they can do something about it.

RickRans

You posted the same link as two days ago... Look at the Author and date and suggested solution at the website. There is also NOTE at the link about option “Remove personal information from file properties on save” that at some circumstances may be checked again by itself (if you use Document Inspector or from other reason) and cause the problem again. Everything seems to lead to that option. Even Microsoft wrote about this on this forum. Has anyone else checked that?

Rafał.Ś

I did a little more Google searching after my last post. The problem is not gone, its just waiting for specific conditions. The description of the problem is here where is shows up differently:

   http://www.excel-first.com/cannot-open-pivot-table-source-file/

The cause appears to be related to the privacy setting and Document Inspector as summarized in this extract from the above line:

___________________________________________________________

The bug is caused by the Document Inspector…

Jon Peltier wrote an article about this problem, way back in 2010 and provided

a workaround developed by Bill Manville, which basically consists in:

◾ Make a copy of the worksheet with the old pivot table and pivot chart in a

different workbook;

◾ Move the copied worksheet back into the original workbook;

◾ Change the new chart’s source data to the new pivot table;

◾ Change the pivot table’s data source to the new range;

◾ Refresh the pivot table.

What if you have a large number of pivot tables and charts? You will have to

work hard to make all these steps for each pivot table and for each chart…

___________________________________________________________

I think the problem will show up when some user action changes this setting which I found in

[MS-OE376]:

Office Implementation Information for ECMA-376 Standards Support

Examples of such changes could be:

• sharing the workbook with a user who has a different version of excel
• changing the Document Inspector settings default
• changing the Document Inspector settings before sharing the document
• switching versions of excel yourself
• saving the spreadsheet as a different version format to share
• etc.

I haven't checked if these all cause the problem, but its probably the reason the problem is somewhat random.

As a minimum it looks like Microsoft should provide a warning of this setting on saving as to its effects and give the user a way to make the setting be what they need.

I think I didn’t  express myself accurately at first. First time I had this problem, when I switched from MS Office 2007/Windows 7 to MS Office 2013/Windows 10 last year. For some time I had to use macro every time I changed filename to get pivot tables working. I also found that, some files (newly created) worked fine without that bug. I went once more time to the forum , I linked earlier and found that remark about option "Remove personal information from file properties"  in Trust Center Privacy settings. I didn’t see that earlier, although it was there. I tried this uncheck before running a macro and it solved the problem for each file it was applied. I got confirmed from other users I share files with, that problem disappeared. I also checked files that worked without problems from start. I found out that these files have option "Remove personal information from file properties"  unchecked as default. When I was writing post here about that “Step 1”, I meant only that option. I also found answer on this forum about this option which I'm inserting here.

„Hi Francesco,

With a little more research, we have a root cause. When you right-click to create the file, the option for "Remove personal information from file properties" is selected in Trust Center Privacy settings. I've tested removing that option before creating the PivotTable, and that stops the issue from happening. So that'd be your workaround.

Regards,

Anita”

It was posted here almost two years ago and nobody has responded to that until now. Well in my case it works. Probably there was a change I assume, to get that option uncheck as default in Windows 10 as it was answered from … Microsoft.

Hi Rick,

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.

I've has this problem using Windows 10 and Excel 2016. A little while after I posted my comment above, the problem went away, without any specific action on my part. I'd say this supports my contention that the problem is "pseudo-random", in the sense that it's caused by some unknown combination of conditions that can come and go unpredictably.