Jerry Nixon on Windows: Use VSTO 3 to create a PivotChart

Jerry Nixon on Windows

Wednesday, November 19, 2008

Use VSTO 3 to create a PivotChart

If you have already created a PivotTable in Excel 2007, adding a PivotChart to it is easy. Assuming you have a local variable that refers to the PivotTable called "_PivotTable" this is all the code you need. The Pivot Chart will copy the column/row/measure settings of the PivotTable. Here's how you do it.
 

// create pivot chart

Globals.Sheet3.Shapes.AddChart(
XlChartType.xlColumnClustered,
System.Type.Missing,
System.Type.Missing,
System.Type.Missing,
System.Type.Missing).Select(System.Type.Missing);
Globals.ThisWorkbook.ActiveChart.SetSourceData(
_PivotTable.TableRange1,
System.Type.Missing);
Globals.ThisWorkbook.ShowPivotChartActiveFields = false;