Export (0) Print
Expand All

Create PowerPoint 2010 Chart with New Chart Object Model

Office 2010

Office Quick Note banner

Getting Started with Office 2010 Extensibility: Learn how to programmatically automate how to create a chart in PowerPoint 2010 from Excel 2010 data.

Last modified: March 29, 2011

Applies to: Excel 2010 | Office 2010 | PowerPoint 2010 | Word 2010

In this article
Create a Presentation and Add Code to the Visual Basic Editor
Add a Reference to the Excel OM Library
Test the Project
Add a Button and Additional Code to the Presentation
Test the Project
Next Steps
Additional Resources

Published:   September 2010

Provided by:    Frank Rice, Microsoft Corporation

Click to view video    Watch the Video

Beginning with the 2007 Microsoft Office system SP2, and now with Microsoft Office 2010, there is a unified and consistent charting object model (OM) within Word and PowerPoint. The chart is drawn by the same shared Office drawing layer so if you are familiar with the Chart OM in Excel 2007, you can easily create a similar solution in Word 2010 or PowerPoint 2010. In this exercise, you will programmatically create a chart in PowerPoint 2010 from Excel 2010 data by using the new Chart OM. This lab consists of adding code to a presentation that creates and populates an Microsoft Excel workbook and then uses that data to populate a new chart in PowerPoint. Next, you add a button and additional code to the presentation to add a label to the chart data. To do this, complete the following:

In this task, you create the blank PowerPoint 2010 presentation and add code to the Visual Basic Editor (VBE).

To create the PowerPoint 2010 presentation

  1. Start PowerPoint 2010 to create the blank presentation.

  2. On the Developer tab, click Visual Basic. This opens the VBE.

  3. On the Insert menu, click Module.

  4. In the module window, add the following code.

    Sub CreateChart()
    Dim myChart As Chart
    Dim gChartData As ChartData
    Dim gWorkBook As Excel.Workbook
    Dim gWorkSheet As Excel.Worksheet
    
    ' Create the chart and set a reference to the chart data.
    Set myChart = ActivePresentation.Slides(1).Shapes.AddChart.Chart
    Set gChartData = myChart.ChartData
    
    ' Set the Workbook and Worksheet references.
    Set gWorkBook = gChartData.Workbook
    Set gWorkSheet = gWorkBook.Worksheets(1)
    
     ' Add the data to the workbook.
    gWorkSheet.ListObjects("Table1").Resize gWorkSheet.Range("A1:B5")
    gWorkSheet.Range("Table1[[#Headers],[Series 1]]").Value = "Items"
    gWorkSheet.Range("a2").Value = "Coffee"
    gWorkSheet.Range("a3").Value = "Soda"
    gWorkSheet.Range("a4").Value = "Tea"
    gWorkSheet.Range("a5").Value = "Water"
    gWorkSheet.Range("b2").Value = "1000"
    gWorkSheet.Range("b3").Value = "2500"
    gWorkSheet.Range("b4").Value = "4000"
    gWorkSheet.Range("b5").Value = "3000"
    
    ' Apply styles to the chart.
    With myChart
        .ChartStyle = 4
        .ApplyLayout 4
        .ClearToMatchStyle
    End With
    
    ' Add the axis title.
    With myChart.Axes(xlValue)
        .HasTitle = True
        .AxisTitle.Text = "Units"
    End With
     
    'myChart.ApplyDataLabels
    
    ' Clean up the references.
    Set gWorkSheet = Nothing
    ' gWorkBook.Application.Quit
    Set gWorkBook = Nothing
    Set gChartData = Nothing
    Set myChart = Nothing
    
    End Sub
    
    

In this task, you add a reference to the Excel 2010 object library.

To add a reference to the Excel 2010 OM library

  1. On the Tools menu, click References.

  2. In the References dialog box, scroll until you see Microsoft Excel 14.0 Object Library, select it, and then click OK.

  3. Close the VBE.

In this task, you run the code and ensure that the workbook is created and populated, and then ensure that the chart is created and populated.

To test the project

  1. On the Developer tab, click Macros, click CreateChart, and then click Run.

  2. Ensure that the workbook is created and data was added. The presentation should also have a chart with data that resembles Figure 1.

    Figure 1. Chart is added to the presentation

    Chart is added to the presentation

In this task, you add a button to slide 1 and code to the VBE that adds a label to the chart.

To add a button and code to the presentation

  1. On the Developer tab, in the Controls group, click the command button control, and then click on the slide to insert the button.

  2. Double-click the button to open the VBE.

  3. Add the following code to the CommandButton1_Click subroutine.

    ' Set a reference to the chart object.
    Set myChart = ActivePresentation.Slides(1).Shapes(1).Chart
    
    ' Add and format a title.
    myChart.HasTitle = True
    With myChart.ChartTitle
        .Characters.Font.Size = 18
        .Text = "2010 Inventory"
    End With
    
    'Clean up.
    Set myChart = Nothing 
    
    
  4. Close the Visual Basic Editor.

In this task, you run the code from the button that adds a data label to the chart.

To test the project

  1. Press F5.

  2. Click the command button. Notice that labels have been added to the data similar to Figure 2.

    Figure 2. Label is added to the chart

    Label is added to the chart
Show:
© 2014 Microsoft