Carl Colijn
New Member
- Joined
- Jun 23, 2011
- Messages
- 32
Hi all,
I've got an interesting but complicated question here. The context is Excel 2007 (with SP2 - 12.0.6545.5004) on Windows XP; the workbook has been build from scratch in Excel 2007 (i.e. no converted 2003 pivots).
I have created a workbook which users can export derived (data-filtered) workbooks from. The workbook contains pivots based on hidden data sheets, but when the data filtering would result in no records being left for a pivot I remove the pivot altogether (since a pivot cannot be based on zero records).
Once in a while the beta-tester users report that the generated filtered workbooks are corrupt; when opening these files Excel asks:
"Excel found unreadable content in workbook "<WORKBOOK'S name>". Do you want to recover the contents of this workbook?"
When selecting "Yes" Excel tells me:
"Removed Records: PivotTable report from /xl/workbook.xml part (Workbook)"
and the resulting workbook is totally OK.
I finally laid my hands on one of the workbooks that gave rise to this error. In this case the exporting functionality very infrequently also causes a
"Automation error - the object invoked has disconnected from its clients"
after which Excel just crashes. I have not yet pinpointed where this crash occurs; after adding file logging the problem hasn't re-occured yet...
By eliminating sheets and pivots I found out the pivots are at fault. The stripped workbook contains just one sheet with one pivot on it, and an item in the pivot is selected. When I then run the code as-is, the resulting workbook will be corrupt. But when I select a cell outside the pivot and then re-select the same cell in the pivot, the code works flawlessly!
Since I couldn't troubleshoot it further from there I saved both the problematic version and the "cured" version of the workbook, unzipped both and did a diff on their contents. The only relevant change is that in the XML for the sheet containing the pivot the "sick" version contains the fragment:
while the "cured" version misses the pivotSelection tag completely. Note that cell A5 is located in the pivot table.
In this case the filtering done by the export removes this pivot table completely (there would be no data left). Comparing the resultant exported workbooks from both the cured and sick versions I found that BOTH contain the same pivotSelection tag, thus also the one where the pivot has actually been removed?! It seems as though the pivotSelection info has creeped into the sick version, and once there it wants to stay there, causing an error if there is no accompanying pivot table anymore.
So my question is: what causes the above XML tag to appear? Selecting a cell outside the pivot and then again in the pivot only causes this tag to disappear and not re-appear. Doing it via VBA by the way unfortunately didn't do the trick. I now remove the pivot by calling
(where oPivotSheet only contains the one pivot table). When I add
in front of that the problem goes away. So for now that is my solution, but what causes these "stray" pivotSelection tags to appear anyway?
Thanks in advance,
Carl Colijn
I've got an interesting but complicated question here. The context is Excel 2007 (with SP2 - 12.0.6545.5004) on Windows XP; the workbook has been build from scratch in Excel 2007 (i.e. no converted 2003 pivots).
I have created a workbook which users can export derived (data-filtered) workbooks from. The workbook contains pivots based on hidden data sheets, but when the data filtering would result in no records being left for a pivot I remove the pivot altogether (since a pivot cannot be based on zero records).
Once in a while the beta-tester users report that the generated filtered workbooks are corrupt; when opening these files Excel asks:
"Excel found unreadable content in workbook "<WORKBOOK'S name>". Do you want to recover the contents of this workbook?"
When selecting "Yes" Excel tells me:
"Removed Records: PivotTable report from /xl/workbook.xml part (Workbook)"
and the resulting workbook is totally OK.
I finally laid my hands on one of the workbooks that gave rise to this error. In this case the exporting functionality very infrequently also causes a
"Automation error - the object invoked has disconnected from its clients"
after which Excel just crashes. I have not yet pinpointed where this crash occurs; after adding file logging the problem hasn't re-occured yet...
By eliminating sheets and pivots I found out the pivots are at fault. The stripped workbook contains just one sheet with one pivot on it, and an item in the pivot is selected. When I then run the code as-is, the resulting workbook will be corrupt. But when I select a cell outside the pivot and then re-select the same cell in the pivot, the code works flawlessly!
Since I couldn't troubleshoot it further from there I saved both the problematic version and the "cured" version of the workbook, unzipped both and did a diff on their contents. The only relevant change is that in the XML for the sheet containing the pivot the "sick" version contains the fragment:
HTML:
<sheetView tabSelected="1" workbookViewId="0">
<selection activeCell="A5" sqref="A5"/>
<pivotSelection pane="bottomRight" showHeader="1" click="1" r:id="rId1">
<pivotArea dataOnly="0" labelOnly="1" fieldPosition="0">
<references count="1">
<reference field="1" count="0"/>
</references>
</pivotArea>
</pivotSelection>In this case the filtering done by the export removes this pivot table completely (there would be no data left). Comparing the resultant exported workbooks from both the cured and sick versions I found that BOTH contain the same pivotSelection tag, thus also the one where the pivot has actually been removed?! It seems as though the pivotSelection info has creeped into the sick version, and once there it wants to stay there, causing an error if there is no accompanying pivot table anymore.
So my question is: what causes the above XML tag to appear? Selecting a cell outside the pivot and then again in the pivot only causes this tag to disappear and not re-appear. Doing it via VBA by the way unfortunately didn't do the trick. I now remove the pivot by calling
Code:
Call oPivotSheet.Rows.Delete
Code:
Call oPivotSheet.PivotTables(1).ClearTableThanks in advance,
Carl Colijn
Last edited:
), which doesn't hold a selection as in which cell is selected, but instead it contains which items are filtered. It was indeed set for the problem pivot table.
