Update Chart Data

  • Article

Office Quick Note banner

Programmatically Working with Office 2010 Charts: Learn how to update a Microsoft PowerPoint 2010 chart by adding data.

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

In this article
Add a Standard Module to a PowerPoint Presentation
Add the Code to the Visual Basic Editor
Test the Solution
Next Steps

Published:   March 2011

Provided by:    Frank Rice, Microsoft Corporation

Microsoft Office 2010 includes a powerful and versatile charting engine. You can chose from many common business and technical chart types. You can also enhance and manipulate the appearance of your charts programmatically by using various classes and objects. In this topic, you programmatically create a simple chart and then update it by adding data. To complete this task, you must do the following:

  • Add a Standard Module to a PowerPoint Presentation

  • Add the Code to the Visual Basic Editor

  • Test the Solution

Add a Standard Module to a PowerPoint Presentation

In this task, you open a PowerPoint 2010 presentation, open the Visual Basic Editor, and then insert a standard module.

To add a standard module to a PowerPoint presentation

  1. Start PowerPoint 2010.

  2. On the Developer tab, click Visual Basic to open the Visual Basic Editor.

    Note

    If you do not see the Developer tab in PowerPoint 2010 (or Word 2010), click the File tab, and then click Options. In the categories pane, click Popular, select Show Developer tab in the Ribbon, and then click OK.

  3. On the Insert menu, click Module. This adds Module1 to the Projects pane on the left side of the Visual Basic Editor.

Add the Code to the Visual Basic Editor

In this task, you add programming code that creates the simple chart with a small amount of data and then adds data to the chart. Note that the procedures in this topic work with a PowerPoint 2010 presentation. You can also work with a Microsoft Word 2010 document by replacing ActivePresentation.Slides(1) with ActiveDocument. All of the remaining code works exactly the same in Word 2010.

To add code to the Visual Basic Editor

  1. In the Projects pane, click Module1.

  2. Paste or type the following Microsoft Visual Basic for Applications (VBA) code into the module window.

    Sub UpdateChartData()
    Dim shp As Shape
    Set shp = ActivePresentation.Slides(1).Shapes.AddChart(xl3DColumn)

    Dim cht As Chart
    Set cht = shp.Chart

    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet

    Set wb = cht.ChartData.Workbook
    Set ws = wb.Worksheets(1)

    ' The size and shape of the data will vary depending
    ' on the type of chart you have created. You will need
    ' to modify this code, depending on your chart. In every
    ' case, Excel creates a ListObject named Table1 for your data.
    ws.Range("A2").Value = "North"
    ws.Range("A3").Value = "South"
    ws.Range("A4").Value = "East"
    ws.Range("A5").Value = "West"

    ws.Range("B1").Value = "2009"
    ws.Range("C1").Value = "2010"
    ws.Range("D1").Value = "2011"

    ' Now expand the table and add more data:
    ws.ListObjects("Table1").Resize ws.Range("A1:E6")
    ws.Range("A6").Value = "Canada"
    ws.Range("B6").Value = "5"
    ws.Range("C6").Value = "4"
    ws.Range("D6").Value = "3"

    ws.Range("E1").Value = "2012"
    ws.Range("E2").Value = "4"
    ws.Range("E3").Value = "5"
    ws.Range("E4").Value = "2"
    ws.Range("E5").Value = "3"
    ws.Range("E6").Value = "6"

    ' Note that the chart now contains more data!
End Sub

  3. The data for the chart is contained in a Microsoft Excel 2010 workbook. To create the workbook in code, you must add a reference to the Microsoft Excel 14.0 Object Library. On the Visual Basic Editor menu, click Tools, and then click References.

  4. In the References dialog box, select Microsoft Excel 14.0 Object Library, and then click OK.

  5. Close the Visual Basic Editor.

Test the Solution

In this task, you run the VBA code that creates the chart and adds data to it. The code also opens a Microsoft Excel workbook that contains the chart data. To see the code in action, you step through it line-by-line.

To run the code and create the chart

  • On the Developers tab, click Macros, select UpdateChartData, and then click Run. Examine the new chart; it is similar to the chart in Figure 1.

    Figure 1. Running the code creates a new chart and updates the chart data

    Code creates new chart and updates the chart data

The best way to see the code in action is to place the Visual Basic Editor window next to the PowerPoint window and then single-step through each line of code.

To step through the code

  1. Open a new PowerPoint 2010 presentation.

  2. Open the Visual Basic Editor, insert a module, and then paste the code described previously into the module.

  3. Add a reference to the Microsoft Excel 14.0 Object Library to the project.

  4. Add a breakpoint to the code. To add the breakpoint, right-click the following line of code, select Toggle, and then click Breakpoint.

    ws.Range("A2").Value = "North"

  5. Drag the Visual Basic Editor window to the right side of your monitor.

  6. Drag the PowerPoint window to the left side of your monitor and adjust both windows until you can see them both.

  7. Click the Visual Basic Editor window and then press the F5 key to run the code. The code starts and then pauses at the breakpoint.

  8. Press F8 to step through the code line-by-line; watch the code create the chart and modify the chart data in the PowerPoint window.

Next Steps