PivotCache.CreatePivotChart method (Excel)

Creates a standalone PivotChart from a PivotCache object. Returns a Shape object.

Syntax

expression.CreatePivotChart (ChartDestination, XlChartType, Left, Top, Width, Height)

expression A variable that represents a PivotCache object.

Parameters

Name Required/Optional Data type Description
ChartDestination Required Variant The Destination worksheet.
XlChartType Optional Variant The type of chart.
Left Optional Variant The distance, in points, from the left edge of the object to the left edge of column A (on a worksheet) or the left edge of the chart area (on a chart).
Top Optional Variant The distance, in points, from the top edge of the topmost shape in the shape range to the top edge of the worksheet.
Width Optional Variant The width, in points, of the object.
Height Optional Variant The height, in points, of the object.

Return value

Shape object

Remarks

If the PivotCache object that the method is called from has no attached PivotTable:

  • A workbook-level PivotTable is created from the existing PivotCache.

  • A standalone PivotChart is created with a reference to the newly created PivotTable.

If the PivotCache already has an associated PivotTable:

  • The PivotCache is cloned.

  • A new workbook-level PivotTable is created based on the cloned PivotCache.

  • A standalone PivotChart is created with a reference to the new workbook-level PivotTable.

Example

The following code creates a decoupled PivotChart from a PivotCache object.

Workbooks("Book1").Connections.Add _
     "cubes4 Adventure Works DW 2008 Special Char Adventure Works", "", Array( _
     "OLEDB;Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Data Source=<server name here >;Initial Catalog=Adventure Works DW 2008" _
     , " Special Char"), Array("Adventure Works"), 1
   ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
     ActiveWorkbook.Connections( _
     "cubes4 Adventure Works DW 2008 Special Char Adventure Works"), Version:= _
     xlPivotTableVersion14).CreatePivotChart(ChartDestination:="Sheet1").Select

   ActiveChart.ChartType = xlColumnClustered

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.