Class PivotTables (Excel VBA)

A collection of all the PivotTable objects in the specified workbook. To use a PivotTables class variable it first needs to be instantiated, for example

Dim pts as PivotTables
Set pts = ActiveSheet.PivotTables()

For Each

Here is an example of processing the PivotTables items in a collection.

Dim pvt As PivotTable
For Each pvt In Application.PivotTables
	
Next pvt

Add

Adds a new PivotTable report. Returns a PivotTable object.

Add (PivotCache, TableDestination, TableName, ReadData, DefaultVersion)

Dim cnnConn As ADODB.Connection 
Dim rstRecordset As ADODB.Recordset 
Dim cmdCommand As ADODB.Command 
 
' Open the connection. 
Set cnnConn = New ADODB.Connection 
With cnnConn 
 .ConnectionString = _ 
 "Provider=Microsoft.Jet.OLEDB.4.0" 
 .Open "C:\perfdate\record.mdb" 
End With 
 
' Set the command text. 
Set cmdCommand = New ADODB.Command 
Set cmdCommand.ActiveConnection = cnnConn 
With cmdCommand 
 .CommandText = "Select Speed, Pressure, Time From DynoRun" 
 .CommandType = adCmdText 
 .Execute 
End With 
 
' Open the recordset. 
Set rstRecordset = New ADODB.Recordset 
Set rstRecordset.ActiveConnection = cnnConn 
rstRecordset.Open cmdCommand 
 
' Create PivotTable cache and report. 
Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _ 
 SourceType:=xlExternal) 
Set objPivotCache.Recordset = rstRecordset 
 
ActiveSheet.PivotTables.Add _ 
 PivotCache:=objPivotCache, _ 
 TableDestination:=Range("A3"), _ 
 TableName:="Performance" 
 
With ActiveSheet.PivotTables("Performance") 
 .SmallGrid = False 
 With .PivotFields("Pressure") 
 .Orientation = xlRowField 
 .Position = 1 
 End With 
 With .PivotFields("Speed") 
 .Orientation = xlColumnField 
 .Position = 1 
 End With 
 With .PivotFields("Time") 
 .Orientation = xlDataField 
 .Position = 1 
 End With 
End With 
 
' Close the connections and clean up. 
cnnConn.Close 
Set cmdCommand = Nothing 
Set rstRecordSet = Nothing 
Set cnnConn = Nothing

Arguments

The following arguments are required:

PivotCache (PivotCache) - The PivotTable cache on which the new PivotTable report is based. The cache provides data for the report.

TableDestination (PivotTables) - The cell in the upper-left corner of the PivotTable report's destination range (the range on the worksheet where the resulting report will be placed). You must specify a destination range on the worksheet that contains the PivotTables object specified by expression.

Optional arguments

The following arguments are optional

TableName (String) - The name of the new PivotTable report.

ReadData (Boolean) - True to create a PivotTable cache that contains all records from the external database; this cache can be very large. False to enable setting some of the fields as server-based page fields before the data is actually read.

DefaultVersion - The version of Microsoft Excel that the PivotTable was originally created in

Count

Returns a Long value that represents the number of objects in the collection.

Dim lngCount As Long
lngCount = ActiveSheet.PivotTables.Count

Item

Returns a single object from a collection.

The text name of the object is the value of the Name and Value properties.

Item (Index)

Index: The name or index number for the object.

Dim pvt As PivotTable
Set pvt = ActiveSheet.PivotTables(Index:=1)