PivotCache.CreatePivotTable (Excel)

Creates a PivotTable report based on a PivotCache object. Returns a PivotTable object.

For an alternative way to create a PivotTable report based on a PivotTable cache, see the Add method of the PivotTables object.

CreatePivotTable (TableDestination, TableName, ReadData, DefaultVersion)

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

Arguments

The following argument is required

TableDestination (PivotCache) - 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.

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 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 - The default version of the PivotTable report