Follow us on Twitter
Follow us in Facebook
Office Dev Content
SharePoint Dev Content
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.
1: Sub AddChart_Excel()
2: Dim objShape As Shape
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)
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.
1: Sub AddChart_Word()
2: Dim objShape As InlineShape
6: Set objShape = ActiveDocument.InlineShapes.AddChart(XlChartType.xlColumnStacked100)
11: objShape.Chart.SetSourceData Source:="'Sheet1'!$A$1:$C$3"
The Chart object in Word 2007 SP2 and PowerPoint 2007 SP2 does have a few differences from the Excel 2007 SP2 implementation.
1: Sub ShowWorkbook_Word()
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
11: ' Activate the topmost window of the application used to
12: ' display the data for the chart.
15: ' Minimize the application used to display the data for
16: ' the chart.
17: objShape.Chart.ChartData.Workbook.Application.WindowState = -4140
18: End If
20: End Sub