PivotCaches.Create method (Excel)

Creates a new PivotCache.


expression.Create (SourceType, SourceData, Version)

expression A variable that represents a PivotCaches object.


Name Required/Optional Data type Description
SourceType Required XlPivotTableSourceType SourceType can be one of these XlPivotTableSourceType constants: xlConsolidation, xlDatabase, or xlExternal.
SourceData Optional Variant The data for the new PivotTable cache.
Version Optional Variant Version of the PivotTable. Version can be one of the XlPivotTableVersionList constants.

Return value



The following two XlPivotTableSourceType constants are not supported when creating a PivotCache by using this method: xlPivotTable and xlScenario. A run-time error is returned if one of these two constants is supplied.

The SourceData argument is required if SourceType isn't xlExternal. It should be passed a Range object (when SourceType is either xlConsolidation or xlDatabase) or a WorkbookConnection object (when SourceType is xlExternal).

When passing a Range object, we recommend that you either use a string to specify the workbook, worksheet, and cell range, or set up a named range and pass the name as a string. Passing a Range object may cause "type mismatch" errors unexpectedly.

When not supplied, the version of the PivotTable will be xlPivotTableVersion12. The use of the xlPivotTableVersionCurrent constant is not allowed and returns a run-time error if it is supplied.


The following code sample defines a connection, and then creates a connection to a PivotCache.

Workbooks("Book1").Connections.Add2 _
    "Target Connection Name", "", Array("OLEDB;Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Data Source=##TargetServer##;Initial Catalog=Adventure Works DW", ""), 
    "Adventure Works", 1
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:=ActiveWorkbook.Connections("Target Connection Name"), _ 

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.