We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now


vba automation between Excel and PowerPoint - dynamic PP charts

Medium Priority
Last Modified: 2012-05-06
I have a large automation project which I took on, not realizing I may be going down a dead end.  I have done a lot of Excel automation projects, but this is the first time I'm working with PowerPoint, and it is less than desirable.

So, the client has a PowerPoint template with about 120 slides, about 45 are dynamic, having text or graphs on them that need to be modified based on data I'm importing into Excel.  So I want to open an instance of the template, modify all the text and graphs and save as something else.

I can open and replace all text, looping through each slide.  BUT, I have a bunch of slides with multiple graphs.  And there doesn't seem to be any way I can distinguish which graph is which.  From the PP interface, I can't see the name of the graph, or the ID, which I can access from excel/vba.  So I can't see which is which.

I have to let the client hand me the template, and what I'd like to be able to do is go to properties or something on the graph object and see which graph is which.

And it seems like very few people have worked with PP before.  I've worked with Access and Word, as many others have, but not PP.  

If I can't see which graph is which, is there at least some logic where I can figure out which is which, like where it is placed on the slide?  My guess is no, that the number is generated when it was created, not by where it is placed.

Has anybody done a project like this?
Watch Question

Top Expert 2011
Noting the lack of response ...

I assume each graph is located in a frame, (shape) therefore in theory you can address the specific shape and insert a 'new' graph from the data via VBA.  To see which frame is which you can of course select the frame on the presentation and then in the VBE use:

application.ActiveWindow.Selection.ShapeRange(1).Name.  The name can then of course be referenced in your excel sheet to replace the graph.


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


Thanks Chris.

You are correct, there is a dearth of PowerPoint support.  Do you program in PP much? I'm just getting into PP, and have much to learn.  But for now, I am going to be controlling PP from Excel.  

In the interest of general knowledge, I am posting a routine and supporting routine that allows the user to open a PP file and it will loop through each slides and put a "label" of the MSGraph name.  

Wow, what a pain.  If you have any other useful tidbits, like a good website/forum, that would be helpful.


Public Sub labelMSGraphs()
    ' go through slides and get obj type 7 and place the name above them
    Dim i, j, iCount, iRow, iCol As Integer
    Dim iSlideNum As Integer
    Dim sText As String
    Dim sFileName As String
    Dim bNewSlide As Boolean
    Dim oPPTApp As PowerPoint.Application
    Dim oPPTShape As PowerPoint.Shape
    Dim oPPTFile As PowerPoint.Presentation
    Dim oGraph As Graph.Chart
    Dim strPresPath As String, strExcelFilePath As String, strNewPresPath As String
    sFileName = Application.GetOpenFilename
    'if user cancels
    If sFileName = "False" Then Exit Sub
    Set oPPTApp = CreateObject("PowerPoint.Application")
    oPPTApp.Visible = msoTrue
    'open file, will be read only... PP is single session
    Set oPPTFile = oPPTApp.Presentations.Open(sFileName)
    'make sure all shapes are ungrouped, so we can access each
    'Call unGroupShapes(oPPTFile)
    'loop through slides
    For Each opptslide In oPPTFile.Slides
        iCount = iCount + 1
        iRow = iRow + 1
        iSlideNum = opptslide.SlideIndex
        'loop through shapes in slide
        For Each oPPTShape In opptslide.Shapes
            iRow = iRow + 1
            '.Cells(iRow, 2).Value = oPPTShape.Name
            '.Cells(iRow, 3).Value = oPPTShape.Type
            'check if is graph object
            If oPPTShape.Type = msoEmbeddedOLEObject Then
                'ok, it's OLE, now check if chart
                If Left(UCase(oPPTShape.OLEFormat.progID), 7) = "MSGRAPH" Then
                    'LABEL IT
                    'Set oPPTShape = opptSlide
                    Debug.Print "Shape name: " & oPPTShape.Name & " -- Top: " & oPPTShape.Top
                    oPPTFile.Slides(iSlideNum).Shapes.AddTextbox(msoTextOrientationHorizontal, _
                        Left:=oPPTShape.Left, Top:=oPPTShape.Top - 15, Width:=200, Height:=50).TextFrame.TextRange.Text _
                        = oPPTShape.Name
                End If
            End If
    MsgBox "DONE!", vbOKOnly + vbInformation
    Set oGraph = Nothing
    Set oPPTShape = Nothing
    Set oPPTFile = Nothing
    Set oPPTApp = Nothing
End Sub
Public Sub unGroupShapes(ByVal oPres As PowerPoint.Presentation)
    'this will ungroup any grouped shapes
    Dim iCount As Integer
    Debug.Print "Beginning UngroupShapes routine. Presentation: " & oPres.Name
    iCount = 0
    For Each oslide In oPres.Slides
        iCount = iCount + 1
        For Each oShape In oslide.Shapes
            If oShape.Type = msoGroup Then
                'Debug.Print "Ungrouping shape: " & oShape.Name
            End If
    Debug.Print "slides: " & Str(iCount)
End Sub

Open in new window


I have also been doing very little with Powerpoint, but this is an interesting question. As Chris pointed out, you can tag the shapes with meaningful names in the template. Wouldn't that work? About the position on the slide, you can use left and top properties of the shape.



Thanks Stellan.  

I did use top and left (line 54 in the code).  I raised it up a bit from the top.  It works pretty well, although it is still a pain that I can't view it directly from PP.

Thanks all.
Top Expert 2011

>>> If you have any other useful tidbits, like a good website/forum

I wish!  i'm just starting to read developing skills in VBA to Powerpoint, it carries similar issues to outlook it seems for the lack of a recorder.  At least common skills have some benefit but it still seems to be a new area and although I have picked up odds and ends from t'internet I haven't found a good centre of excellence, (to rival Sue Mosher for outlook).

That said glad we were able to help a little and good luck with your project.

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.