How to delete a PivotCache?

[feipezi]

Hi,
Since it's difficult to re-fresh a PT created by ADO from a MS Access database, I set up a Excel macro to re-create a PT based on the external data source but the workbook gets bigger as more PivotCache added.
Is there any way of removing those PivotCaches? I know usual '.Delete' is not supported.
Thanks in advance.
John Z.

 

[SkipVought]

Hi,

Assuming the PT is in Excel...

Sub CleanMyPivots()
'SkipVought/2007 Apr 30
'--------------------------------------------------
'cleans up pivot tables having OLD/UNUSED data hanging around.
'--------------------------------------------------
    Dim pc As PivotCache
    For Each pc In ActiveWorkbook.PivotCaches
       pc.MissingItemsLimit = xlMissingItemsNone
       pc.Refresh
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Skip,
I tried your code but I got '1004' on 'pc.Refresh' statement. I guess Excel couldn't find what it was looking for. In fact, I have 4 pc's in the WorkBook (after I counted).
Is your 200704 code in this forum?
Thanks again.

 

[SkipVought]

this procedure does run without error. I just checked in a new workbook. Check Tools > References in the VB Editor for MISSING.

"Is your 200704 code in this forum?"

No. This procedure/function is one of many that I have in a module that I publish to my users at work, that was originally published on 2007 Apr 30.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Skip,
Sorry but the old issue came up again. I still haven't solved the problem yet.
I need to update some PT report monthly with new data. If I cannot remove the existing PivotCache, the report will get bigger and bigger. I tried your code before and now I still have '1004' on 'pc.Refresh'.
I checked. .MissingItemsLimit = -1 before the statement .MissingItemsLimit = xlMissingItemsNone, which gave me 0. But 0 has nothing to do with the removal of PC.
Thanks in advance.

 

[combo]

If the PivotTable cache isn't referenced by a PivotTable object, the PivotTable cache is automatically deleted before the workbook is saved.

You could try to save pivot table without underlying data, set SaveData (for pivot table) to False.

combo