Referencing Powerpoint graphs using VB in an Excel macro

Posted on 2007-08-01
Last Modified: 2009-04-17
Hey all!

I'm using the below in a macro to automatically port data from excel into powerpoint. The code lies in the excel workbook.

It works well but i have got to the stage where there are multiple graphs on one slide and i'm not sure how to reference them. at the moment the below just uses the slide reference (oPPTApp.ActivePresentation.Slides(3))  to get the graph.

Question- is it possible to reference more than one graph using VB?  



Sub UpdateGraph()
   Dim oPPTApp As PowerPoint.Application
   Dim oPPTShape As PowerPoint.Shape
   Dim oPPTPres As PowerPoint.Presentation
   Dim rngNewRange As Excel.Range
   Dim oGraph As Object

' Set oPPTApp to PowerPoint by creating a new instance of PowerPoint.
' If PowerPoint is already open, you would instead use the GetObject
' method instead.
   Set oPPTApp = CreateObject("PowerPoint.Application")
' Set PowerPoint to be Visible.
   oPPTApp.Visible = msoTrue
' Open Presentation1.ppt from My Documents.
   oPPTApp.Presentations.Open "C:\My Documents\Presentation1.ppt"
' Set rngNewRange to the collection of cells in the active Excel
' workbook and active sheet.
   Set rngNewRange = ActiveSheet.Range("E11:E17")
' Select the range then copy it.
' On slide one of Presentation1.ppt, loop through each shape.
   With oPPTApp.ActivePresentation.Slides(3)
      For Each oPPTShape In .Shapes
' Check to see whether shape is an OLE object.
         If oPPTShape.Type = msoEmbeddedOLEObject Then
' Check to see whether OLE object is a Graph 2000 object. The ProgID
' is case sensitive.
            If oPPTShape.OLEFormat.ProgId = "MSGraph.Chart.8" Then
' Set oGraph to the Graph object on the slide.
               Set oGraph = oPPTShape.OLEFormat.Object
' Paste the cell range into the upper leftmost cell of the graph
' datasheet. This position is designated "00" (two zeros). To designate
' a range to start in the second row, first column, you would use "01".
' Likewise first row, second column is "A0". This will also link the
' datasheet to the Excel Workbook cell range. If you do not want to
' link to the Workbook, just omit the word "True". The default
' choice for the Paste method is "False".
               oGraph.Application.DataSheet.Range("A1").Paste False

            End If

         End If
        Next oPPTShape
    End With
End Sub
Question by:skinnyl
    LVL 58

    Accepted Solution

    It looks as though you are simply finding existing chart objects and updating their datasheets. In that case, you can give meaningful names to them and refer to them by name and not by number. From the immediate pane of PowerPoint's VB, try:

        ? ActivePresentation.Slides(3).Shapes(4).Name

    It might say "Object 7". This is the (unique) name of that shape. To make it more readable, you can rename it:

        ActivePresentation.Slides(3).Shapes(4).Name = "Overall Pie Chart"

    Then you can directly use that name in your code:

        ' get the pie chart
        Set oGraph = oPPTApp.ActivePresentation.Slides(3) _
            .Shapes("Overall Pie Chart").OLEFormat.Object

    Note that you still need to know the slide number or name ("Slide 3"). You can likewise rename your slides, in case you later reorganize them. From Powerpoint's immediate pane:

        ActivePresentation.Slides(3).Name = "Overview"

    Then, you can use:

        ' get the "overall pie chart" on the "overview" slide:
        Set oGraph = oPPTApp.ActivePresentation.Slides("Overview") _
            .Shapes("Overall Pie Chart").OLEFormat.Object

    This will still work if you insert a slide before this one or if you remove another chart from that slide. The same syntax can be used to refer to any number of charts on the same slide or on different slides.

    Finally, you should probably add the library "Microsoft Graph ?.?? Object Library" in (Tools | References) so that you can write:

        Dim oGraph As Graph.Chart

    This enables early binding and might make your programming a little easier.

    Does that help?

    Author Comment

    thanks for the reply!

    Yes, I'm not trying to create any new charts, just update a 'template' if you will.

    I see what your saying about the names, make good sense!

    How to i discover the unique number of the shape (which i assume is a chart?) in order to assign it with a name? Also, i'm doing this from Excel in a macro. Powerpoint doesn't get a look in apart from being told what to do by Excel.

    I understand that 'Shapes(4)' refers to the chart, just wonder where 4 comes from?

    Thanks again

    LVL 58

    Expert Comment

    I really just played with it:

        ? ActivePresentation.Slides(3).Shapes.Count
        ? ActivePresentation.Slides(3).Shapes(1).Name


    You can also record a macro in Powerpoint where you select a chart and change something. Reading the macro will show the current name of the shape, which is just as useful as the number.

    Note: don't forget to delete the module when done, or record the macro in a copy of your presentation.

    Your own code can be used as well. Examine the shapes in turn with something like:

        ? ActivePresentation.Slides(3).Shapes(7).OLEFormat.ProgId

    At least you will know it's a chart, even if you don't know which one.

    Good luck!
    LVL 21

    Expert Comment

    To use VB to name shapes:
    And this one might help, it shows how to cycles through the shapes on a slide determining which is a graph object:;EN-US;qQ267974

    Author Comment

    Sorry about the delay, got a bit sidetracked!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now