Chart Object Model in Word & PowerPoint

Over the next three posts this blog will cover some of the charting changes that have been made in Office 2010, many of which are the direct result of customer feedback.  

As part of the Office 2007 release we overhauled charting throughout Office; however an OM for Word and PowerPoint was not provided. In response to your feedback, in Office 2007 SP2 and Office 2010 we’ve exposed a unified/consistent charting OM within Word and PowerPoint, so that you can write solutions against any of the applications that take advantage of the new charting capabilities. The chart is drawn by the same shared Office drawing layer so if you are familiar with the OM in Excel 2007 you can easily create a similar solution in Word or PowerPoint.

Many times companies have a need to create charts in documents or presentations based on client specific data. In many cases these charts have a standard format, size and position which make them a prime candidate for automation. This OM can be used as a part of a solution to automate the generation of these charts which can save many hours of manual labor.

Let’s take a look at an example. Here is an example of how to create a chart in PowerPoint based on data your application has retrieved and apply standard formatting.

Sub CreateChart()

Dim myChart As Chart

Dim gChartData As ChartData

Dim gWorkBook As Excel.Workbook

Dim gWorkSheet As Excel.Worksheet

Set myChart = ActivePresentation.Slides(1).Shapes.AddChart.Chart 'create/set chart

Set gChartData = ActivePresentation.Slides(1).Shapes(1).Chart.ChartData 'Set chartdata

Set gWorkBook = gChartData.Workbook 'Set Workbook object reference

Set gWorkSheet = gWorkBook.Worksheets(1) 'Set Worksheet object reference

gWorkSheet.ListObjects("Table1").Resize gWorkSheet.Range("A1:B5") 'Add Data

gWorkSheet.Range("Table1[[#Headers],[Series 1]]").Value = "Sales"

gWorkSheet.Range("a2").Value = "Bikes"

gWorkSheet.Range("a3").Value = "Accessories"

gWorkSheet.Range("a4").Value = "Repairs"

gWorkSheet.Range("a5").Value = "Clothing"

gWorkSheet.Range("b2").Value = "1000"

gWorkSheet.Range("b3").Value = "2500"

gWorkSheet.Range("b4").Value = "4000"

gWorkSheet.Range("b5").Value = "3000"

With myChart 'Apply Style

    .ChartStyle = 4

    .ApplyLayout 4

    .ClearToMatchStyle

End With

myChart.HasTitle = True 'Add Title

With myChart.ChartTitle 'Format title

    .Characters.Font.Size = 18

    .Text = "2007 Sales"

End With

With myChart.Axes(xlValue) 'Add axis title

    .HasTitle = True

    .AxisTitle.Text = "$"

End With

myChart.ApplyDataLabels 'Add data labels

Set gWorkSheet = Nothing

gWorkBook.Application.Quit

Set gWorkBook = Nothing

Set gChartData = Nothing

Set myChart = Nothing

End Sub

As you can see a chart is still represented by a ChartObject and in this case is contained by a Shape. It can be contained by either an InlineShape or Shape in Word and a Shape in PowerPoint. From there the ChartObject is generally a mirror of what you would see in Excel. Some key differences are:

• Properties/Methods that normally accept a range object in Excel now accept a string of the range address in Word and PowerPoint.
• A new object, ChartData, has been added to Word and PowerPoint to access the underlying linked or embedded data for a chart.

The ability to programmatically manipulate charts in Word and PowerPoint also opens the door for a variety of interesting solutions around dynamically changing the appearance of your charts during a presentation or creating an interactive Word document. Here is an example of a solution to add data labels to a chart during a presentation:

Private Sub BtnDataLabels_Click()

Set myChart = ActivePresentation.Slides(1).Shapes(1).Chart 'Set chart object reference

myChart.ApplyDataLabels 'Add data labels

Set myChart = Nothing

End Sub

Try it out and let us know what you think!