Microsoft Office Tutorials and References
In Depth Information
20.8 The PivotCache Object
ActiveSheet.PivotTables("Sales&Trans"). _
PivotFields("Store City").Subtotals(2) = True
ActiveSheet.PivotTables("Sales&Trans"). _
PivotFields("Store City").Subtotals(3) = True
We can also set the Subtotals property to an array of 12 Boolean values to set multiple subtotals.
For instance, the following code displays all subtotals:
ActiveSheet.PivotTables("Sales&Trans"). _
PivotFields("Store City"). _
Subtotals = Array(False, True, True, True, True, _
True, True, True, True, True, True, True)
Note that we set Automatic to False in this array, since if Automatic is set to True , then all
other values are set to False (thus providing a quick way to set all subtotals to False ).
If this argument is omitted, the Subtotals method returns an array containing a Boolean value for
each subtotal.
20.8 The PivotCache Object
Pivot tables can manipulate the source data in a variety of ways, and this can require a great deal
of processing power. For maximum efficiency, the data for a pivot table is first stored in memory
in what is referred to as a pivot cache . The pivot table, itself, actually provides various views of
the pivot cache. This allows manipulation of the data without the need to access the original
source further which might, after all, be an external data source.
The PivotCache object represents a pivot table's cache. It is returned by the PivotCache method of
the PivotTable object. Let us take a look at some of the main properties and methods of the
PivotCache object.
20.8.1 Refreshing a Pivot Cache
The Refresh method refreshes both the pivot cache and the pivot table.
However, we can prevent a pivot table from being refreshed, either through the user interface (the
Refresh data menu item on the PivotTable menu) or through code, by setting the EnableRefresh
property to False .
The read-write Boolean RefreshOnFileOpen property is True if the PivotTable cache is
automatically updated each time the workbook is opened by the user. The default value is False .
Note that this property is ignored if the EnableRefresh property is set to False . Note also that the
PivotTable cache is not automatically refreshed when the workbook is opened through code, even
if RefreshOnFileOpen is True .
The RefreshDate property returns the date on which the pivot cache was last refreshed, and the
RefreshName property returns the name of the user who last refreshed the cache.
20.8.2 MemoryUsed Property
The read-only MemoryUsed property applies to either a PivotCache object or a PivotField object
and returns the amount of memory currently being used by the cache, in bytes.
Search JabSto ::

Custom Search