[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2167
  • Last Modified:

vba automation between Excel and PowerPoint - dynamic PP charts

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?
0
dougfosterNYC
Asked:
dougfosterNYC
  • 2
  • 2
1 Solution
 
Chris BottomleyCommented:
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.

Chris
0
 
dougfosterNYCAuthor Commented:
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.

Thanks.

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(1).
                    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
        
        Next
    Next
    
    
    MsgBox "DONE!", vbOKOnly + vbInformation
    
almostExit:
    'oPPTFile.Close
 
 
exitMe:
    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
                oShape.Ungroup
                'Debug.Print "Ungrouping shape: " & oShape.Name
            End If
        
        Next
    Next
    
    Debug.Print "slides: " & Str(iCount)
 
End Sub

Open in new window

0
 
StellanRosengrenCommented:
Hi
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.

Regards,
Stellan
0
 
dougfosterNYCAuthor Commented:
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.
0
 
Chris BottomleyCommented:
>>> 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.

Chris
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now