Microsoft Office 2000/Visual Basic Programmer's Guide

Creating PivotChart Reports

You can create a PivotChart report that's associated with a PivotTable report so that users can view data in a graph as well as in a table. A PivotTable report and PivotChart report are linked, so that pivoting on data in the PivotTable report updates the PivotChart report, and vice versa.

To create a PivotChart report from code, you add a new chart to the workbook and call the SetSourceData method of the chart, passing in a reference to the PivotTable range. You can use the TableRange1 and TableRange2 properties to return ranges representing part or all of the PivotTable report. The TableRange1 property returns a range representing the PivotTable report but excluding page fields; the TableRange2 property returns a range representing the entire PivotTable report, including page fields.

The following code creates a PivotChart report from a worksheet named Regional Sales.

Private Sub cmdChart_Click()
   Dim chtNew       As Excel.Chart
   Dim lngWksCount  As Long
   
   ' Count number of worksheets in workbook and add chart to end.
   lngWksCount = ThisWorkbook.Sheets.Count
   ' Add new chart.
   Set chtNew = _
      ThisWorkbook.Charts.Add(After:=ThisWorkbook.Sheets(lngWksCount))
   With chtNew
      .Name = REGIONAL_SALES & " Chart"
      ' Set chart's data source to be PivotTable report.
      chtNew.SetSourceData _
         Source:=Sheets(REGIONAL_SALES).PivotTables(1).TableRange2
      ' Set chart type.
      chtNew.ChartType = xlColumnClustered
   End With
End Sub

This procedure appears in the module for the frmPivotOptions form, which is available in the ODETools\V9\Sampes\OPG\Samples\CH15 subfolder on the Office 2000 Developer CD-ROM. The form includes a button that the user can click to create a PivotChart report.