Creating a Pivot Table Report Using the Create PivotTabie Method of the Pivot Cache Object

When you use the macro recorder to generate the code for creating a PivotTable programmatically, Excel uses the Add method of the PivotCaches collection to create a new PivotCache. A PivotCache object represents the data behind a PivotTable. It is an area in memory where data is stored and accessed as required from a data source.

The example procedure connects to the Microsoft Access Northwind database using the MicrosoftJet.OLEDB.4.0 provider. To use this type of connection, you must set up a reference to the Microsoft ActiveX Data Objects (ADO) in the References dialog box (available in the Microsoft Excel Visual Basic Editor screen).

After establishing a connection with a database and executing the SQL statement to obtain the data, the procedure creates a PivotCache using the following line of code:

Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _ SourceType:=xlExternal)

The code then places the data from the external data source in the PivotCache by assigning a Recordset object to the PivotCache object, like this:

Set objPivotCache.Recordset = rst

Next, the code uses the CreatePivotTable method of the PivotCache object to create an empty PivotTable:

With objPivotCache

.CreatePivotTable TableDestination:=Range("B6"), _ TableName:="Invoices"

End With

Once the skeleton of the PivotTable is created, the code adds appropriate fields to the PivotTable.

You should use the PivotCache when you need to generate multiple PivotTables from the same data source. By using a PivotCache, you can gain a high level of control over your external data source. The PivotCache object can also be used to change and refresh data stored in the cache. The last several lines of the example procedure demonstrate how to find out information about the PivotCache.

To force the PivotCache to refresh automatically when the workbook file containing the PivotTable is opened, set the RefreshOnFileOpen property to True. Add the following statement at the end of the procedure shown below:

ActiveSheet.PivotTables("Invoices").PivotCache.RefreshOnFileOpen = True

1. Add a new module to the current workbook and enter the following procedure code.

2. Run the procedure to generate the PivotTable.

Sub Pivot_External2()

Dim objPivotCache As PivotCache Dim cmd As New ADODB.Command Dim rst As New ADODB.Recordset Dim dbPath As String dbPath = "C:\Program Files\Microsoft Office\Office\" & _

"Samples\Northwind.mdb" With cmd

.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" &

"Data Source =" & dbPath .CommandText = "Select Country, ProductName, " & _ "ExtendedPrice from Invoices" End With

Set rst = cmd.Execute

' Create a PivotTable cache and report

Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _

SourceType:=xlExternal) Set objPivotCache.Recordset = rst

Worksheets.Add With objPivotCache

.CreatePivotTable TableDestination:=Range("B6"), _ TableName:="Invoices"

End With

' Add fields to the PivotTable With ActiveSheet.PivotTables("Invoices") .SmallGrid = False With .PivotFields("Country") .Orientation = xlRowField .Position = 1 End With

With .PivotFields("ProductName") .Orientation = xlRowField .Position = 2 .Name = "Product Name" End With

With .PivotFields("ExtendedPrice") .Orientation = xlDataField .Position = 1

.NumberFormat = "$#,##0.00" End With End With

' Autofit columns so all headings are visible ActiveSheet.UsedRange.Columns.AutoFit

' Clean up Set cmd = Nothing Set rst = Nothing

' Obtain information about PivotCache With ActiveSheet.PivotTables("Invoices").PivotCache Debug.Print "Information about the PivotCache:" Debug.Print "Number of Records: " & .RecordCount Debug.Print "Data was last refreshed on: " & .RefreshDate Debug.Print "Data was last refreshed by: " & .RefreshName Debug.Print "Memory used by PivotCache: " & .MemoryUsed & _ " (bytes)"

End With End Sub

+1 0

Post a comment