C
Carl Colijn
Note: I already have a thread open on this issue at MrExcel's forum at:
http://www.mrexcel.com/forum/showthread.php?p=2762897
but not much has come out of it yet, therefore I re-post it here
summarized.
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 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:
<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>
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
Call oPivotSheet.Rows.Delete
(where oPivotSheet only contains the one pivot table). When I add
Call oPivotSheet.PivotTables(1).ClearTable
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?
-- findings due to question --
The associated pivot cache itself does get properly removed in the
workbook, so that one isn't at fault.
-- problem revisited --
Alas, contrary to what I thought this didn't really/always fix the
problem.
The pivotSelection XML tag in the xlsm source led me to the VBA
PivotTable.PivotSelection method (who could have guessed?
), 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.
When I added:
Call oPivotSheet.PivotTables(1).ClearAllFilters
in front of the already added PivotTable.ClearTable call, the resulting
..xlsm workbook was not corrupt anymore.
It thus seems that when a pivot table is filtered, and when you
consecutively remove the table in VBA by deleting all cells on the
sheet, the pivot's filtering is remembered behind the scenes, which
causes the workbook to be 'corrupt' when you re-open the workbook. This
was for Excel 2007, SP 2.
Problem (again) fixed, I hope... But does anyone have any experience
with this and thus can tell me if the "fix" I applied will hold?
--
Thanks in advance,
Carl Colijn
TwoLogs - IT Services and Product Development
A natural choice!
http://www.twologs.com
TimeTraces: the powerful and versatile time registration system!
http://timetraces.twologs.com
http://www.mrexcel.com/forum/showthread.php?p=2762897
but not much has come out of it yet, therefore I re-post it here
summarized.
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 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:
<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>
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
Call oPivotSheet.Rows.Delete
(where oPivotSheet only contains the one pivot table). When I add
Call oPivotSheet.PivotTables(1).ClearTable
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?
-- findings due to question --
The associated pivot cache itself does get properly removed in the
workbook, so that one isn't at fault.
-- problem revisited --
Alas, contrary to what I thought this didn't really/always fix the
problem.
The pivotSelection XML tag in the xlsm source led me to the VBA
PivotTable.PivotSelection method (who could have guessed?
), whichdoesn'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.
When I added:
Call oPivotSheet.PivotTables(1).ClearAllFilters
in front of the already added PivotTable.ClearTable call, the resulting
..xlsm workbook was not corrupt anymore.
It thus seems that when a pivot table is filtered, and when you
consecutively remove the table in VBA by deleting all cells on the
sheet, the pivot's filtering is remembered behind the scenes, which
causes the workbook to be 'corrupt' when you re-open the workbook. This
was for Excel 2007, SP 2.
Problem (again) fixed, I hope... But does anyone have any experience
with this and thus can tell me if the "fix" I applied will hold?
--
Thanks in advance,
Carl Colijn
TwoLogs - IT Services and Product Development
A natural choice!
http://www.twologs.com
TimeTraces: the powerful and versatile time registration system!
http://timetraces.twologs.com