Referencing PowerPoint Object in Excel VBA

Question

Long story short - I am trying to pass a string to PowerPoint from PowerShell to update a textbox on a master slide. I can do this from within PowerPoint vba, but it is completely static. As soon as I try to pass data through PowerShell to PowerPoint, I get an error that I have been unable to solve. (see here)

So instead I am attempting to do it through an Excel proxy! I already use Excel VBA to update content on PowerPoint slides, so thought this would be suitable. I currently have shapes set up in Excel, data is updated, and then this is pasted in to a PowerPoint deck, saved and closed.

I need to now extend this Excel macro with functionality that updates the textbox on the master slide. I need to translate "PowerPoint VBA" to "Excel VBA for PowerPoint", what a horrible sentence... I digress:

PowerPoint VBA

Function UpdateMasterF(message As Variant)
    Dim sourceLabel As Shape
    Dim curSLD As Long
    curSLD = ActiveWindow.View.Slide.SlideIndex

    'switch to SlideMaster
    Application.Windows(1).ViewType = ppViewSlideMaster

    Set sourceLabel = ActivePresentation.SlideMaster.CustomLayouts(1).Shapes("sourcelabel")
    sourceLabel.TextFrame.TextRange.Text = message

    'return to default
    Application.Windows(1).ViewType = ppViewNormal

    'set slide
    ActiveWindow.Presentation.Slides(curSLD).Select

Excel VBA

Function TestPowerPoint(message As Variant, presPath As Variant)
    Dim oPPTApp As Object
    Dim oPPTFile As Object

    Set oPPTApp = CreateObject("PowerPoint.Application")
    oPPTApp.Visible = msoTrue
    Set oPPTFile = oPPTApp.Presentations.Open(presPath)

    ' translate e.g. ApplicationWindow, ActivePresentation etc

    oPPTFile.Save
    oPPTFile.Close
    Set oPPTFile = Nothing
    oPPTApp.Quit
    Set oPPTApp = Nothing

I need to be able to complete the same steps as in the PowerPoint VBA but in the Excel macro. I'm having issues finding the right names for it though. So where it says

Application.Windows(1).ViewType = ppViewSlideMaster

Can this be replaced with oPPTApp or oPPTFile? I've read through MSDN Docs and it seems to match but doesn't work.

Hopefully that's conveyed my ask! I expect most people to read and shudder at such a situation...

Do you *need* to switch views? I know you would if you were doing it manually, but if you're referencing `ActivePresentation.SlideMaster.CustomLayouts(1)...` then **perhaps** you don't need to? I've no idea though, I've never written to the Master. — CLR, Mar 30 '17 at 14:43
Everything from PowerPoint you'll need to declar. e and reference using the appropriate PowerPoint object. So instead of Application.Windows(1).ViewType = ppViewNormal you'll need oPPTApp.Windows(1).ViewType. I'd also declare oPPTApp as type PowerPoint.Application. Make sure you Reference the PowerPoint object library. — Rich Holton, Mar 30 '17 at 14:43
@CLR - the only reason for the manual parts was to make sure the right master slide was being selected, as it has 3. If I can find one line to directly update the box on that slide I'd happily do that. Will research. Manually the quickest way is to view the slide and press master slide, which takes you to the master for that slide. — Chris, Mar 30 '17 at 14:53
What I meant was, I wasn't sure if you needed to find the Excel equivalent of the lines: `Application.Windows(1).ViewType = ppViewSlideMaster` and `Application.Windows(1).ViewType = ppViewNormal` at all. Best of luck! — CLR, Mar 30 '17 at 14:55
@RichHolton PowerPoint.Application doesn't seem to be a valid type, am I understanding correctly? Dim oPPTApp As PowerPoint.Application — Chris, Mar 30 '17 at 14:59
Have you added the Power Point object library to your list of References? (Tools->References in the VBA Editor) — Rich Holton, Mar 30 '17 at 15:05
No I hadn't - thanks for that. I can use the autocomplete for the object structure now. It doesn't seem to have any of the actual objects though (slide,master,etc) is it possible to see these in the VBA editor as well? I guess the object hasn't actually been loaded so it doesn't count. — Chris, Mar 30 '17 at 15:13
try PowerPoint.slide, etc. I've used Excel and Word this way. I'm assuming PowerPoint works the same way. — Rich Holton, Mar 30 '17 at 15:16
that works Richard. After looking through the docs again it seems this PowerPoint.Application is a "level" up in the docs, and isn't really able to interrogate the file opened in it, which is a shame. Not sure what I'm trying to do is possible. It seems I want PowerPoint.Master, PowerPoint.Slide etc which are on the same level, not objects nested within Application. Does that make sense? — Chris, Mar 30 '17 at 15:36

score 2 · Answer 1 · answered Mar 30 '17 at 19:06

The hierarchy's like this:

To access a presentation open in PPT, you'd use e.g.

Set oPPTPres = oPPTApp.Presentations(1) or
Set oPPTPres = oPPTApp.ActivePresentation

Each presentation object has a Designs collection representing the different masters in the presentation, so to access SlideMaster of the first design,

With oPPTPres.Designs(1).SlideMaster
   ' for example:
   With .Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 100, 500, 50)
      .TextFrame.TextRange.Text = "Well, look at that, will ya!"
   End With

End with

Each Design has a collection of custom layouts (the thumbnails you see indented beneath the main master when in Slide Master view). They can be similarly accessed.

Steve, this was a fantastic help, thank you so much. I think I've very nearly cracked it. I was using With oPPTPres.Slides(3).CustomLayout With .Shapes("sourcelabel").TextFrame.TextRange.Text = message it completed without error (first time for everything!) but the box didn't update. I looked at the documentation and the CustomLayout object seems to be read-only, would this be why? I'm going to try going through the Designs object as you have suggested. — Chris, Mar 31 '17 at 08:14

score 0 · Answer 2 · answered Mar 31 '17 at 13:09

So extending Steve's answer, I decided to just replace the existing box with a new one, to save the pain of trying to edit the existing shape.

I'm fortunate that the automation I am doing is for the creation of new PowerPoint decks, so I'm able to just place these new shapes on them as I create multiple decks from one blank canvas.

The code I ended up with to get the same formatting is:

Function TestPowerPoint(message1 As String, message2 As String, presPath As Variant)

set up variables to be used
Dim oPPTApp As PowerPoint.Application
Dim oPPTFile As Object
Dim oPPTPres As PowerPoint.Presentation

Set oPPTApp = CreateObject("PowerPoint.Application")
oPPTApp.Visible = msoTrue
Set oPPTFile = oPPTApp.Presentations.Open(presPath)
Set oPPTPres = oPPTApp.ActivePresentation

With oPPTPres.Designs(1).SlideMaster.CustomLayouts(1)
    With .Shapes.AddTextbox(msoTextOrientationHorizontal, 28, 60, 500, 25)
        .ZOrder msoBringToFront
        With .TextFrame.TextRange
            .Text = message1
            With .Font
                .Size = 10
                .Name = "Calibri"
                .Color = RGB(255, 0, 0)
                .Bold = msoTrue
            End With
        End With
    End With
End With

With oPPTPres.Designs(1).SlideMaster.CustomLayouts(2)
    With .Shapes.AddTextbox(msoTextOrientationHorizontal, 28, 200, 736, 50)
        .ZOrder msoBringToFront
        With .TextFrame.TextRange
            .Text = message2
            .ParagraphFormat.Alignment = ppAlignCenter
            With .Font
                .Size = 32
                .Name = "Calibri"
                .Color = RGB(255, 255, 255)
                .Bold = msoFalse
            End With
        End With
    End With
End With

oPPTFile.Save
oPPTFile.Close
Set oPPTFile = Nothing
oPPTApp.Quit
Set oPPTApp = Nothing

End Function

Fantastic! Thank you to everyone who contributed.

Bingo ... nice job of taking my last post and running with it. If ever you need to retrieve a shape that you've previously added (to edit or to delete before adding a new shape), look up Tags. — Steve Rindsberg, Mar 31 '17 at 16:42

Referencing PowerPoint Object in Excel VBA

2 Answers2