Can’t Open Pivot Table Source…
Like a good Excel fan, I switched to Excel 2016 to try the new version.
- 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.
- 3) If you get a rar file as an email attachment, tapping the file will open it in Easy Unrar. 4) If the file is on your phone, open Easy Unrar and go to the folder which has your file.
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:
Change Data Source
After moving a pivot table or a chart, the reference contains the file name:
When I try to open a pdf file on Adobe Reader XI (or even in Adobe Acrobat XI!!!) which has a.zip or a.tar. file attached to it, I get the message: Acrobat cannot save the file attachment 'file.zip' because your PDF file attachment settings do not allow this file type to be saved.
And this is just the beginning… ?
The file is unusable, any time a user changes the name, the pivot table will fail to refresh. Space burger mac os.
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:
xlpivotCache_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.
The normal content of this file should look like this:
PivotCache Definition
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/xlPathMissing
Now, 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 xlpivotCache 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 xlpivotCache_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:
Remove Relationship
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:
As 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
Working with Excel Functions and Formulas »
Dec 21, 2020 • Filed to: Recover & Repair Files • Proven solutions
Q: Why Can't I Open My Zip File?
'I downloaded a Zip file and tried to open it to no avail. All I keep getting is an error message. Please, can anyone tell me why this is happening and how I can fix it?'
'I downloaded a Zip file and tried to open it to no avail. All I keep getting is an error message. Please, can anyone tell me why this is happening and how I can fix it?'
One of the most commonly used formats for compression of files is the Zip format, represented as .zip file extension. It is a single file that contains a set of compressed files. When files are compressed into Zip format, their sizes get considerably reduced which makes for easier transfer and use of less space. However, the files have to be unzipped before you can view them. It becomes an issue if the Zip file you need to view does not open.
This article is available to give you all the information you need on why you cannot open your Zip file. Some great software will be suggested for you to use in unzipping your Zip files. Finally, you will learn about Stellar File Repair and how you can use it to open your Zip files which have been corrupted by one factor or the other.
Part 1: Why Can I Not Open Zip File?
![Zip Zip](https://www.wikihow.com/images/d/d0/Open-a-.Zip-File-Without-Winzip-Step-25.jpg)
In this part, you will find out the major reasons why you cannot open your Zip file. The reasons for compressed or Zip files not opening include the following:
- Incomplete Downloads: Zip files may refuse to open if they are not properly downloaded. Also, incomplete downloads occur when files get stuck due to issues like bad internet connection, inconsistency in network connection, all of which can cause errors in transfer, affect your Zip files and make them unable to open.
- Improper Compression: A file compression can get halted due to unforeseen circumstances such as abrupt power failure or low battery which can cause a system shutdown. This could corrupt the file undergoing the compression process. Consequently, the Zip file may become damaged and refuse to open.
- File Corruption: This is a major reason why your Zip files are proving difficult to open. A particular corruption that stops the opening of Zip files is the header corruption. A Zip file has a feature called 'header' which is responsible for keeping information such as the file name, the date you created it, file size, and so on. The information in a header is essential in opening or accessing the contents contained within the file. This only goes to say that if the header is corrupt, there will be the challenge of the Zip file not opening.
- Changed File Extension: A Zip file can be seriously damaged if its extension is changed to another format which is not supported on Windows. A Zip file that is severely damaged may not open when you are trying to extract its data.
- Virus Infections: The infestation caused by the virus is always enough reason for one type of error or the other and the case is not different from Zip files. A virus or malware infection which is externally introduced to a Zip file archive will cause a replication of the file's contents, thereby corrupting it. It has been made clear so far that if any of your Zip files gets corrupted, then you may be faced with the challenge of your Zip file, not opening.
- Other Reasons: If your Zip file is stored in an external storage device and the device gets damaged from exposure to such hazards as magnetic fields, dangerously high temperature, or even mechanical shock, it can affect the contents of the drive, including the Zip file in it. This can lead to corruption and your inability to open the Zip, even if the drive eventually gets repaired. Also, another reason could be if you forget the password to your encrypted archive file. However, this is only applicable if you originally assigned a password to your Zip file.
Part 2: Which zip file can be opened on Windows?
There are many archive file formats out there such as Rar, TZ, GZ, Zip, 7Zip, BZip, and so on. It can be overwhelming trying to choose which format to use, especially if you are not sure the file can be opened on your Windows. Therefore, this part will show you the three major Zip files that can be opened on Windows as discussed below:
- Zip: This archive format is ideal for large files to be transferred because you can compress more than one file into it, thereby reducing the time and resource wastage that comes with transferring large files. It also helps in keeping similar files in a single location. Zip is regarded as the most common compression format used on Windows for the sharing and emailing of files. Your Windows can internally handle Zip files without you having to seek the help of external software or extra tool.
- ZipX: This type of Zip file is more advanced than the normal Zip file format. ZipX files contain several files that are compressed into the extended version of Zip format and in smaller sizes than the previous Zip formats. This is where the advanced function comes into play. ZipX was developed by CorelWinZip (WinZip 12.1) and it can be opened on Windows.
- 7Z: This file compressed format (also known as 7-Zip) was created by Igor Pavlov with the use of various compression utilities. 7Z file has a high ratio of compression and strong AES-256 encryption. It enables you to transfer Zip files easily while saving storage space for your system. It is interesting to note that the 7Z file format was introduced by Windows itself so you are assured it can be opened on your Windows.
Part 3: Try another unzipper
If your Zip files are not opening, you can try using one or more of the many available programs online that are specially designed to zipping and Unzip archive files. Below is several recommended software for you:
![Zip Zip](https://i.ytimg.com/vi/5mJCe1TVPU8/maxresdefault.jpg)
- WinRar: WinRar is a powerful tool that tops the chart of unzippers. It chooses the best compression method to use for your file archiving; creates smaller compressed files which makes it faster than other tools. It can separate your Zip file into parts and save them on different disks.
- PeaZip: This unzipper is a free tool that supports the extraction of data from over 180 file formats. Also, it does not only unzips files as it can create new compressed files in more than 10 different formats. PeaZip is advanced to support the creation of self-extracting files.
- 7-Zip: This software is very popular for file extraction and archiving of popular file formats, including Zip files. This tool has a reliable AES-256 encryption. 7-Zip works in collaboration with Explorer; this way, you get to right-click on an archive file and easily pull it out. However, if you can't open a 7-zip archived file, repair it by following this post.
- ZipGenius: This free tool has been created to be used solely for Windows. With ZipGenius, you can create a password for your newly created archive file. Also, you can separate one large file into several sections of smaller proportions to make it easier for you to transfer and share the files on the web or simply for storage purposes.
Part 4: How to open a corrupted zip file
Bad or corrupt Zip files cannot be opened unless they are repaired and a great way to repair a corrupt Zip file is with the use of Stellar File Repair. This is a professional tool used for the repair of Windows files on PowerPoint, Word, Zip, and Excel. It has an interface that is easy to use so you will not have a challenge performing your Zip file repair and extraction. Just follow these simple steps:
Step 1: Choose the location of your corrupt Zip file
Step 2: Once the corrupt Zip files are displayed, select 'Repair' to start fixing the corrupt files
Step 3: Preview the Zip files after they have been repaired
Step 4: Select a location and save the repaired Zip files there.
Part 5: Common Errors when Opening Zip File
There are several errors associated with the opening of Zip files, convenient as the format may be. Some of the common errors you may encounter when opening Zip files are enumerated below:
- Invalid Archive: This is inarguably one of the most common errors you can be sure to face when opening Zip files. Your Zip file can be rendered invalid if it gets corrupt from an interruption during its download, transfer, or copy. It can also be from internet interruption or bad network during the download of the file.
- Corrupt/Damaged Archive: This error may be caused by over usage of the zip file, infection from a virus, sudden power loss during Zip file extraction, or an increase in the bad sections of the location where the Zip file is located. These factors can lead to loss of important data in the Zip file, corruption, and blockage of access for file contents. It can ultimately prevent you from opening your Zip file.
- Unknown Method of Compression: You can get this error message when you are trying to extract a Zip file:
This file was compressed using an unknown compressing method
This simply means that the compressor version used to compress the Zip file is different from the one you are trying to use to unzip the archive file.
- Invalid/Corrupted Zipped Folder: This is also caused by errors from interrupted downloads due to power interruption, or virus infection. In this case, although the Zip file is corrupt, you may still be able to open it but some of its original data can go missing. Attempting to open such a damaged Zip file will only prompt an error message like this to appear:
Can't Open Zip Files Anymore
- CRC Error: This error occurs from damage to data within your Zip file. CRC is an acronym for Cyclic Redundancy Check. It is a value that is calculated by the compressor and saved in the newly created Zip file. When you open the file, the compressor will calculate the CRC value once again and if it doesn't tally with the one initially created and saved, the CRC error is sure to appear on your screen. This is a way of telling you there is a problem with the data in your Zip file.
Can't Open Zip Files
Rescue & Fix Files
Can't Open Zip File Archive
- Recover Document Files
- Repair Document Files
- Recover & Repair More
- Clear & Delete File