Can’t Open Pivot Table Source…

Like a good Excel fan, I switched to Excel 2016 to try the new version.While working on a Dashboard for a client, with a lot of Pivot Tables and charts in it, at some point, after moving some pivot tables and charts into other sheets, within the same workbook, I received this unexpected error message – Can’t Open Pivot Table Source:I never saw this error before, and I have a 20 years experience in working with Excel. Apparently, the pivot table cannot be Refreshed, because the Data Source reference is not relative anymore, it contains the file name too:Before moving pivot tables and charts:After moving a pivot table or a chart, the reference contains the file name:And this is just the beginning… 🙂The file is unusable, any time a user changes the name, the pivot table will fail to refresh. 

I tried the following solutions, with no result:

 

• manually removing the file name from the Data Source reference in all pivot tables had no effect, the problem still comes back;
• even if I used a code to change the data source for all pivot tables, and then removed duplicate Pivot Caches, the file keeps changing the source reference to absolute.

After 2 days of work, I finally narrowed down the source of the error…

 The problem, which is an Excel bug after my opinion, will appear under these circumstances:

• the excel file was received from another person, and it was created in a previous version of excel, this is one of the cases when the option from Excel Options-Trust Center-Privacy Options: “Remove personal information from file properties on save” is checked.
• the file contains a pivot table, with or without a pivot chart.
• the pivot chart, or the pivot table, (or both), is moved into another sheet (the chart with cut-paste, pivot with the option-Move Pivot Table)

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… 

My solution is in fact a hack, because there is no easy way to fix the workbook…

In previous versions of Microsoft Office, files created in Microsoft Office Excel, Microsoft Office PowerPoint, and Microsoft Office Word were saved in a proprietary, single file format; they were known as binary files.The Office Open XML Formats are based on XML and ZIP archive technologies. However, unlike legacy files, Office Open XML Formats files can be opened to reveal component parts that give you access to the structures that compose the file.To view the structure of the excel file, you can change the file extension from .xlsx to .zip (ignore the warning message) and simply double click the file to open it; don’t forget to change back the extension to .xlsx after you finish editing the file.Or, you can right click the excel file, and Open With WinRaR, WinZip, 7Zip or any other archive software.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.relsIf there are multiple caches, the rest of the files will be: pivotCacheDefinition2.xml.rels, pivotCacheDefinition3.xml.rels and so on.The normal content of this file should look like this: In the broken file, the Pivot Cache Definition will look like this:As you can easily notice, Excel added a new relationship, even there are no external links in the file:http://schemas.microsoft.com/office/2006/relationships/xlExternalLinkPath/xlPathMissingNow, when you open the renamed file, you might get an error message saying that “Data Connections are disabled”, even there there is no external connection.It’s a mistery why Excel considers that we have external content and adds a xlExternalLinkPath/xlPathMissing relationship, and which is the connection with the Document Inspector.If you look in the archive, in xl\pivotCache folder, there are 2 files:pivotCacheDefinition1.xml and pivotCacheRecords1.xml.The pivotCacheDefinition1.xml will contain the following expression (note the relationship ID in red):<worksheetSource name=”DataTable” r:id=”rId2″/>The red part will never be found in a normal file, the normal value should be:<worksheetSource name=”DataTable”/> 

And now, the solution:

Warning: Please apply these steps on a copy of your workbook, in some circumstances you can corrupt the workbook and it cannot be repaired! (as I said it’s mostly a hack)

Step 1:

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

Step 2:

Open the excel archive with an archiver, and go to xl\pivotCache\_rels folder.From the pivotCacheDefinition1.xml.rels file (and from all other pivotCache Definition files), simply delete the red part, but ONLY the red part, otherwise excel will not be able to open it, it will become corrupted:Save the changes and close the file.

Step 3:

Open the excel workbook.All pivot tables data source will have this weird looking reference: !DataTable . Note that the reference to the file name is missing, but there still is an exclamation mark.All you have to do is to click the SAVE button and close the file! Now, when you open the file, the bug is gone forever…

Done!

The relationship ID from pivotCacheDefinition1.xml will not contain the wrong relationship ID r:id=”rId2″, excel took care of that itself, and rewrote the pivotCacheDefinition1.xml.rels.

Note:

If the option from Excel Options-Trust Center-Privacy Options: “Remove personal information from file properties on save” is unchecked, the pivot chart and pivot tables can be moved, the bug will not appear.Excel will check this option automatically if the file was created in a previous version, or if you use the Document Inspector to remove personal information, the next time you open the Privacy Options you will notice that the option is checked, even if it was not checked before using the Document Inspector. If the option is greyed, you can only activate it if you use the Document Inspector.Users should do this on a copy of their file, it’s not bullet proof, if they make a mistake, they can corrupt the file irreversibly, excel will open with attempts to repair the corrupted file.Or, you can download the file below and apply the above steps to fix it, to make sure you understand the process, even if it’s fairly simple:Functional Pivot Table – Broken.xlsxAs a conclusion, I hope that Microsoft engineers will fix this bug, from what I’ve seen, there are many users with this problem, I even found an old topic started in 2013, with no functional solution: data-source-path-in-pivot-table-changes-to-absolute-on-its-own 

An alternative:

 As mentioned above, another way to solve this absolute reference problem is to copy the data source data sheet and the sheets with pivot tables and charts into a new “clean” workbook, and to change the data sources for all pivot tables to the data source from the new workbook, this solution is more accessible to most users.If the workbook is too complex to move all the sheets into a new book, then hacking the archive is the way to fix the current workbook.It is an extreme solution, but it was the only one that worked for me, without the need to manually move the sheets and reconstruct the entire workbook.If this solution worked for you, please share this information 🙂Cheers,Catalin