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.