replace PivotCache in PivotTable containing calculated fields

[Anna]

In Excel XP I am trying figure out a better way to replace the PivotCache in
a Pivot Table containing calculated fields and retain the existing
structure. I have noticed that when the pivot table wizard is used to modify
the query, Excel creates a new PivotCache, modifies the existing index to
point to this Pivot Cache and doesn't lose the pivot fields based on
calculated fields.
So far I keep the list of calculated fields and pivot fields information,
create a new Pivot Table with
the PivotCache that I need, replace pivotindex in my original Pivot Table,
restore lost fields and delete the created Pivot Table.
I guess it's rather clumsy way.
Can anyone shed light on how Excel does it or think about more elegant
solution?

 

[Wei-Dong XU [MSFT]]

Hi Anna,

When the pivottable query is changed from pivottable wizard, the pivottable data in the cells of workbook will be cleaned and the pivotcache index
will not be changed, and the PivotCache object represents the data that is stored in RAM memory behind a PivotTable.

"I have noticed that when the pivot table wizard is used to modify the query, Excel creates a new PivotCache, modifies the existing index to
point to this Pivot Cache and doesn't lose the pivot fields based on calculated fields."

I don't quite understand your description for the pivotCache. Do you mean the origianl data is still kept in the memory after one new query? It will be
greatly appreciated you give me one detailed steps for your scenario description so I can assist you more on this issue.

Best regards,
Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.