Office Chart Object Model in PowerPoint and Word

With the release of Service Pack 2 (SP2) for Microsoft Office 2007, you can now programmatically access and manipulate charts using the VBA object model in Word 2007 SP2 and PowerPoint 2007 SP2. Prior to Service Pack 2, you could only access charts using the VBA object model in Excel 2007.

The chart object in Word 2007 SP2 and PowerPoint 2007 SP2 is drawn by the same shared Office drawing layer implementation used by Excel 2007, so if you’re familiar with the charting object model in Excel 2007, you can easily migrate Excel VBA code that manipulates charts into Word 2007 SP2 and PowerPoint 2007 SP2 VBA code.

In Excel 2007, a chart is represented by the ChartObject object. In Word 2007 SP2 and PowerPoint 2007 SP2, a chart is represented by a Chart object. The Chart object is contained by an InlineShape or Shape (in Word 2007 SP2) and a Shape (in PowerPoint 2007 SP2). In Word 2007 SP2, you can use the InlineShapes collection of the Document object to add new or access existing charts. In PowerPoint 2007 SP2, you can use the Shapes collection of the Slide object to add new or access existing charts. You can use the AddChart method for both collections, specifying the chart type and location within the document or slide, to add a new chart.

You can use the HasChart property to determine if an InlineShape object (InlineShape.HasChart in Word 2007 SP2) or Shape object (Shape.HasChart in PowerPoint 2007 SP2) contains a chart. If HasChart returns True, you can then use the Chart property to get a reference to a Chart object that represents the chart. At this point, the implementation is virtually identical in Excel 2007 SP2, Word 2007 SP2, and PowerPoint 2007 SP2, and VBA code can be transferred across all three programs in most cases.

For example, the following VBA code adds a new 2-D stacked column chart to the active worksheet in Excel and sets the chart’s source data to the range A1:C3 from the Sheet1 worksheet.

Excel 2007 SP2

    1: Sub AddChart_Excel()

    2:     Dim objShape As Shape

    3:     

    4:     ' Create a chart and return a Shape object reference.

    5:     ' The Shape object reference contains the chart.

    6:     Set objShape = ActiveSheet.Shapes.AddChart(XlChartType.xlColumnStacked100)

    7:     

    8:     ' Ensure the Shape object contains a chart. If so,

    9:     ' set the source data for the chart to the range A1:C3.

   10:     If objShape.HasChart Then

   11:         objShape.Chart.SetSourceData Source:=Range("'Sheet1'!$A$1:$C$3")

   12:     End If

   13: End Sub

By comparison, the following VBA code adds a new 2-D stacked column chart to the active document in Word 2007 and sets the chart’s source data to the range A1:C3 from the chart data associated with the chart.

Word 2007 SP2

    1: Sub AddChart_Word()

    2:     Dim objShape As InlineShape

    3:     

    4:     ' Create a chart and return a Shape object reference.

    5:     ' The Shape object reference contains the chart.

    6:     Set objShape = ActiveDocument.InlineShapes.AddChart(XlChartType.xlColumnStacked100)

    7:     

    8:     ' Ensure the Shape object contains a chart. If so,

    9:     ' set the source data for the chart to the range A1:C3.

   10:     If objShape.HasChart Then

   11:         objShape.Chart.SetSourceData Source:="'Sheet1'!$A$1:$C$3"

   12:     End If

   13: End Sub

Key Differences Between the Chart object in Word 2007 SP2/PowerPoint 2007 SP2 and ChartObject object in Excel 2007 SP2

The Chart object in Word 2007 SP2 and PowerPoint 2007 SP2 does have a few differences from the Excel 2007 SP2 implementation.

• Programmatically creating or manipulating a ChartData object in Word 2007 SP2 or PowerPoint 2007 SP2 will cause Excel 2007 SP2 to run.
• Chart properties and methods for manipulating the chart sheet aren’t implemented.
  The concept of a chart sheet is specific to Excel 2007. Chart sheets aren’t used in Word 2007 or PowerPoint 2007, so methods and properties used to reference or manipulate a chart sheet have been disabled for those applications.
• Properties and methods that, in Excel 2007 SP2 normally take a Range object reference now take a range address in Word 2007 SP2/PowerPoint 2007 SP2.
  The Range object in Word 2007 SP2 and PowerPoint 2007 SP2 is different than the Range object in Excel 2007 SP2. To prevent confusion, the charting object model in Word 2007 SP2 and PowerPoint 2007 SP2 accepts range address strings, such as "='Sheet1'!$A$1:$D$5", in those properties and methods (such as the SetSourceData method of the Chart object) that accept Range objects in Excel 2007 SP2.
• A new object, ChartData, has been added to the VBA object models for Word 2007 SP2 and PowerPoint 2007 SP2 to provide access to the underlying linked or embedded data for a chart.
  Each chart has, associated with it, the data used to draw the chart in Word 2007 SP2 or PowerPoint 2007 SP2. The chart data can either be linked from an external Excel workbook, or embedded as part of the chart itself. The ChartData object encapsulates access to the data for a given chart in Word 2007 SP2 or PowerPoint 2007 SP2. For example, the following VBA code displays, then minimizes, the chart data for each chart contained by the active document in Word 2007 SP2:

    1: Sub ShowWorkbook_Word()

    2:     Dim objShape As InlineShape

    3:     

    4:     ' Iterates each inline shape in the active document.

    5:     ' If the inline shape contains a chart, then display the

    6:     ' data associated with that chart and minimize the application

    7:     ' used to display the data.

    8:     For Each objShape In ActiveDocument.InlineShapes

    9:         If objShape.HasChart Then

   10:  

   11:             ' Activate the topmost window of the application used to

   12:             ' display the data for the chart.

   13:             objShape.Chart.ChartData.Activate

   14:             

   15:             ' Minimize the application used to display the data for

   16:             ' the chart.

   17:             objShape.Chart.ChartData.Workbook.Application.WindowState = -4140

   18:         End If

   19:     Next

   20: End Sub