Syntax
expression.CreatePivotTable(TableDestination, TableName, ReadData, DefaultVersion)
expression A variable that represents a PivotCache object.
Parameters
| Name
| Required/Optional
| Data Type
| Description
|
| 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 PivotTable report will be placed). The destination range must be on a worksheet in the workbook that contains the PivotCache 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 of the 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 default version of the PivotTable report. |
Return Value
PivotTable
Remarks
For an alternative way to create a PivotTable report based on a PivotTable cache, see the Add method of the PivotTables object.
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 A3 on the active worksheet.
| Visual Basic for Applications
|
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal) .Connection = _ "OLEDB;Provider=MSOLAP;Location=srvdata;Initial Catalog=National" .CommandType = xlCmdCube .CommandText = Array("Sales") .MaintainConnection = True .CreatePivotTable TableDestination:=Range("A3"), _ TableName:= "PivotTable1" End With With ActiveSheet.PivotTables("PivotTable1") .SmallGrid = False .PivotCache.RefreshPeriod = 0 With .CubeFields("[state]") .Orientation = xlColumnField .Position = 1 End With With .CubeFields("[Measures].[Count Of au_id]") .Orientation = xlDataField .Position = 1 End With End With
|
This example creates a new PivotTable cache using an ADO connection to Microsoft Jet, and then it creates a new PivotTable report based on the cache, at cell A3 on the active worksheet.
| Visual Basic for Applications
|
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 a PivotTable cache and report. Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _ SourceType:=xlExternal) Set objPivotCache.Recordset = rstRecordset With objPivotCache .CreatePivotTable TableDestination:=Range("A3"), _ TableName:="Performance" End With 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
|
create pivot table Create Pivot Table Method createpivot CreatePivotTable CreatePivotTable Method
See also: