INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to delete a PivotCache?

How to delete a PivotCache?

(OP)
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.

RE: How to delete a PivotCache?





Hi,

Assuming the PT is in Excel...

CODE

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: How to delete a PivotCache?

(OP)
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.
 

RE: How to delete a PivotCache?




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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: How to delete a PivotCache?

(OP)
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.
 

RE: How to delete a PivotCache?

Quote (excel vba help):

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close