Adds a new PivotTable report. Returns a PivotTable object.
Syntax
expression.Add (PivotCache, TableDestination, TableName, ReadData, DefaultVersion)
expression A variable that represents a PivotTables object.
Parameters
| 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.
Example
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
.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