CODEWITHSUNDEEP
excelvbapowerpoint
DavidVBA
DavidVBA

Reputation: 25

How to get Excel VBA to Run a PPT Macro WITH Parameters?

I am trying to run a PowerPoint Macro through Excel VBA, I used to be able to run a macro on a powerpoint file with ease but I am having trouble passing a parameter in excel.

Sub Test()
    Dim arr(1 To 1), macname As String, objPP As Object, PPTFilePath As String, ObjPPFile As Object, 
    PPtFileName As String
    PPTFileName  ="Report.pptm"
    PPTFilePath ThisWorkbook.Path & PPTFileName
    Set objPP = CreateObject("PowerPoint.Application")
    objPP.Visible = True
    Set objPPFile = objPP.Presentations.Open(PPTFilePath)
    
    Application.EnableEvents = False
    
    arr(1) = ThisWorkbook.Path
    macname = "'" & PPTFileName & "'!Module3.UpdateSpecificLinks"
    objPP.Run macname, arr
    objPPFile.Save
    waiting (3)
    
    Application.EnableEvents = True
End Sub

I get an error on objPP.Run macname, arr , it is: Run-time error '-2147188160 (80048240)': Application.Run :Invalid request. Sub or Function not defined.

How do I properly Pass a parameter to the powerpoint macro: Sub UpdateSpecificLinks(LNK as String)

Upvotes: 0

Views: 1101

Answers (1)

Steve Rindsberg
Steve Rindsberg

Reputation: 14810

If your SubUpdateSpecificLinks is e.g. in a private module, the call to it will fail; it must be public.

I think this is the problem, though:

macname = "'" & PPTFileName & "'!Module3.UpdateSpecificLinks"

Try this instead:

macname = PPTFileName & "!Module3.UpdateSpecificLinks"

A couple of examples, calling from a PPTM file to another (closed) PPTM file:

Here are the calling macros:

Sub TestWithString()

    Dim sFileName As String
    Dim oPres As Presentation
    
    sFileName = "C:\temp\runme.pptm"
    Set oPres = Presentations.Open(sFileName, , , False)

    Application.Run "C:\temp\RunMe.pptm!RunMe", "This is the passed parameter"
    
    oPres.Close

End Sub

Sub TestWithArray()

    Dim sFileName As String
    Dim oPres As Presentation
    Dim aStrings(1 To 3) As String
    
    sFileName = "C:\temp\runme.pptm"
    Set oPres = Presentations.Open(sFileName, , , False)
    aStrings(1) = "String 1"
    aStrings(2) = "String 2"
    aStrings(3) = "String 3"

    Application.Run "C:\temp\RunMe.pptm!HowAboutAnArray", aStrings
    
    oPres.Close

End Sub

And here are the macros they call:

Sub RunMe(sMsg As String)
    MsgBox "You said " & sMsg
End Sub

Sub HowAboutAnArray(vParm As Variant)
    Dim x As Long
    
    For x = 1 To ubound(vParm)
        MsgBox vParm(x)
    Next

End Sub

Upvotes: 2

Related Questions