Programatically Clear PivotCache Memory?

[MrExcel]

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

    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:

    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 ]

[MrExcel]

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?

[NateO]

Is the following of help?

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

Pulled from:

http://www.microsoft.com/exceldev/articles/movs104.htm

[MrExcel]

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...

[NateO]

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/de...pivotcache.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=cache...&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, NateO

[ This Message was edited by: NateO on 2002-05-17 13:34 ]

[Mark W.]

What happens when you do this...

ActiveSheet.PivotTables("PivotTable1").SaveData = False