Skip to main content

PivotTables.Add method

Adds a new PivotTable report. Returns a PivotTable object.


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

expression A variable that represents a PivotTables object.


Name Required/Optional Data type Description
PivotCache Required PivotCache The PivotTable cache on which the new PivotTable report is based. The cache provides data for the report.
TableDestination Required Variant 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.
TableName Optional Variant The name of the new PivotTable report.
ReadData Optional Variant 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 Optional Variant The version of Microsoft Excel that the PivotTable was originally created in.

Return value

A PivotTable object that represents the new PivotTable report.


This example creates a new PivotTable cache based on an OLAP provider, and then it creates a new PivotTable report based on the cache at cell A1 on the first worksheet.

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
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"), _
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.
Set cmdCommand = Nothing
Set rstRecordset = Nothing
Set cnnConn = Nothing

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>