Programatically Clear PivotCache Memory?

MrExcel

MrExcel

.
Builders Club Member
Joined
Feb 8, 2002
Messages
3,392
Office Version
  1. 365
Platform
  1. Windows
How does a PivotCache get deleted from memory?

I have a procedure that defines a pivot cache and builds two pivot tables. Another procedure modifies the pivot tables. When that procedure is complete, it clears the pivot tables with
Code: 
    PT.TableRange2.Clear
    PT2.TableRange2.Clear

There doesn't seem to be anyway to explicity clear the pivot table cache. I initially thought I didn't have to worry about it, but now I am starting to see memory errors after a day of running these reports.

After deleting the above pivot tables, this code seems to indicate that the PivotCache is gone. There are no messages generated from this:
Code: 
    For Each pc In ActiveWorkbook.PivotCaches
        MsgBox pc.MemoryUsed
    Next pc

Rather than leave this to chance, I would like a way to explicity clear the memory from the pivotcache.

Bill

_________________
MrExcel.com Consulting
This message was edited by MrExcel on 2002-05-17 06:53
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
I added Debug.Print Application.MemoryUsed at the beginning and end of the report.

Even though there are no PivotCaches left in ActiveWorkbook.PivotCaches, I'm clearly taking up a significant higher amount of memory.

Any ideas on how to clear the MemoryUsed?
 
Upvote 0
Is the following of help?

<pre>
Sub Proc06_DisplayPivotCacheProperties()
Dim Pivot1 As PivotTable
Set Pivot1 = Worksheets("Pivot").Pivottables("Pivot1")
With Pivot1.PivotCache
MsgBox "Memory Used: " & .MemoryUsed
MsgBox "Record Count: " & .RecordCount
MsgBox "Refresh Date: " & .RefreshDate
MsgBox "Refresh Name: " & .RefreshName
MsgBox "Connection: " & .Connection
'The following sets the pivottable to refresh on file open.
.RefreshOnFileOpen = True
'The following refreshes the pivottable cache.
.Refresh
End With
End Sub</pre>

Pulled from:

http://www.microsoft.com/exceldev/articles/movs104.htm
 
Upvote 0
I saw that and was able to ascertain the memory being used by the PivotCache. You'll notice they don't address clearing the PivotCache at all.

I am tempted to try creating the pivot table in a temporary workbook. Then, after creating the report, I could close that temporary workbook, which should remove any memory used by that temporary workbook...
 
Upvote 0
The temp file may be the way to go, I'm starting to get the impression that the pivotcache object is read-only. A few findings:

The following link has a lot of useful object property details, including .optimizecache but I didn't notice anything regarding deletion.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl10/html/xlobjpivotcache.asp

I did stumble into the following.

Per Microsoft:

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

http://216.239.39.100/search?q=cach...hAdd.asp+"pivotcache"+"deleted"&hl=en&ie=UTF8

Section: Add method as it applies to the PivotCaches object.

From this I might surmize that if one deletes a pivot table, the cache is deleted and until then it's out there.

Perhaps XL can be of assistance.
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-05-17 13:34
 
Upvote 0
Solution
What happens when you do this...

ActiveSheet.PivotTables("PivotTable1").SaveData = False
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Slicer disconnects from pivottable when programatically filtering items in pivot fields
Replies
2
Views
791
Telefonstolpe
T
ajjava
  • Question
Procedure to find MAX VALUE in a SPECIFIC pivot table value field, in the case of MULTIPLE pivot table value fields
Replies
0
Views
993
ajjava
ajjava
S
  • Question
Excel VBA Getting error when creating pivot chart but error only occurs after sub that creates pivot tables. No error if directly called
Replies
0
Views
691
Steve48
S
S
  • Question
Assistance in a code to clear excel caches in VBA
Replies
1
Views
2K
Micron
M
A
  • Solved
Pivot Table - Column Over Written There Must be a way to
2 3
Replies
21
Views
2K
Arts
A

Forum statistics

Threads
1,216,380
Messages
6,130,274
Members
449,570
Latest member
TomMacca52

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top