Create a blank pivot table connected to you favorite data source (in my case OLAP connection):
Save the file, change the extension to "ZIP", open the zip file and add the following XML to the "pivotTable1.xml" file under the "XL" folder in the newly opened archive file:
(< pivotTableDefinition ...>)
(< location ... />)
<formats count="1"> <format dxfId="0"> <pivotArea /> </format> </formats>
(< etc... >)
Save it, convert the ZIP archive back to a XLSM/XLSX file and attempt to open this in Excel 2010. On multiple machines and configurations, Excel 2010 crashes with the following exception:
Unhandled exception at 0x00007FF65EBBC368 in EXCEL.EXE: 0xC0000005: Access violation reading location 0x0000000000000004
Now, open the file in excel 2016 (or Office 365) and... On multiple machines and configurations you will see no crashing or errors what so ever.
Why, and how do I go about preventing this condition from ever occurring in the first place in the future?
Obviously most users do not edit there excel files by hand, this XML is getting added by excel itself (in some version, I'm not sure which) and then being saved into the file. The demonstration above is the minimum code required to trigger the issue. I've received files with much more formatting XML that exhibits the same behavior. After significant research, I determined that it is this section of the XML that is causing Excel 2010 to crash, immediately upon opening the file.
There is precious little written on excel access violations, most of it having nothing to do with PivotTables. Other than removing this XML by hand from the file, I've yet to come up with any solution to this issue.
User contributions licensed under CC BY-SA 3.0