Pivot Caches

A PivotCache is a buffer, or holding area, where data is stored and accessed as required from a data source. It acts as a communication channel between the data source and the PivotTable.

In Excel 2007, you can create a PivotCache using the Create method of the PivotCaches collection, as seen in the recorded code. You have extensive control over what data you draw from the source when you create a PivotCache. Particularly in conjunction with ADO (ActiveX Data Objects), which is demonstrated at the end of this chapter, you can achieve high levels of programmatic control over external data sources. Chapters 20 and 21 show you the great flexibility of ADO and other ways to handle external data sources. You can use the techniques from those chapters to construct data sources for PivotTables.

You can also use a PivotCache to generate multiple PivotTables from the same data source. This is more efficient than forcing each PivotTable to maintain its own data source.

When you have created a PivotCache, you can create any number of PivotTables from it using the CreatePivotTable method of the PivotCache object.

0 0

Post a comment