Referencing PowerPoint Object in Excel VBA

1
votes

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

2
vbaexcelpowershellms-officepowerpoint
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
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
@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
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
@RichHolton PowerPoint.Application doesn't seem to be a valid type, am I understanding correctly? Dim oPPTApp As PowerPoint.Application – Chris

2 Answers

2
votes

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.

0
votes

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.