PivotCaches.Create method (Excel)
Creates a new PivotCache.
Syntax
expression.Create (SourceType, SourceData, Version)
expression A variable that represents a PivotCaches object.
Parameters
| 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
PivotCache
Remarks
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.
Example
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"), _
Version:=xlPivotTableVersion15).CreatePivotChart(ChartDestination:="Sheet1").Select
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.