vba automation between Excel and PowerPoint - dynamic PP charts

Posted on 2009-02-18
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?
Question by:dougfosterNYC
    LVL 59

    Accepted Solution

    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.


    Author Comment

    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

    LVL 13

    Expert Comment

    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.


    Author Comment

    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.
    LVL 59

    Expert Comment

    by:Chris Bottomley
    >>> 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.


    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Have you ever been sent a PowerPoint presentation file and wondered why it filled your mailbox? Or have you ever sent a PowerPoint presentation by email and received complaints about the size? Or have you ever created a PowerPoint presentation and t…
    Setting the Scene Animations in PowerPoint are a great tool to convey messages when used carefuly with the content of your slides. There are plenty of animation effects and options, including a Repeat feature for individual animation effects. …
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

    729 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