Run Powerpoint sub from Excel VBA

2
votes

I need to run a PowerPoint sub from a sub in Excel. The reason is that most PowerPoint actions run far faster and with less errors when run from a sub in PowerPoint than when run from a sub in Excel.

I am trying to use Application.Run(Macroname_As_String) where I use PptApp in place of Application and PptApp is loaded as:

Dim PptApp As PowerPoint.Application
Set PptApp = CreateObject("PowerPoint.Application")

I tried referring to the VBA script as both Presentation1.pptm!UpdateOLELinks and UpdateOLELinks ie. file and VBA script / just VBA script.

I get the error :

"Method 'Run' of object '_Application' failed".

My VBA script UpdateOLELinks is located in Module1 of Presentation1.

Any Ideas?

4
excelvbapowerpoint
Did you open your presentation in the code? Or is it already open in an existing instance of Powerpoint? Or neither? – R3uK
Yes I opened the presentation in the code with Set Ppt1 = PptApp.Presentations.Open(PptPath, msoFalse, msoTrue, msoTrue). – lolxor
Ok, so it seems that the Run method in powerpoint require parameters : msdn.microsoft.com/fr-fr/library/office/Ff744221.aspx So try something like : PptApp.Run Macroname_As_String, Parameters_As_Array, even if you pass an empty array! – R3uK
Thanks. Now I get an error: "Application (unknown member): Invalid request. Sub or function not defined." I double checked names and made sure the sub in ppt now takes a variable (also double checked that it is public). Any ideas for debugging? – lolxor
Try specifying the module name before the name of the procedure : PptApp.Run Module_Name.Macroname_As_String, Parameters_As_Array – R3uK

4 Answers

0
votes

The Run Method in PowerPoint require parameters : msdn.microsoft.com/fr-fr/library/office/Ff744221.aspx

So, even if you pass an empty array, try something like : 

PptApp.Run Macroname_As_String, Parameters_As_Array

Other untested possibilities (with your references for context) I stumbled across while researching : 

Dim PptApp As PowerPoint.Application
Set PptApp = CreateObject("PowerPoint.Application")
Set Ppt1 = PptApp.Presentations.Open(PptPath, msoFalse, msoTrue, msoTrue)

    'Possibility 1
    PptApp.Run Macroname_As_String, Parameters_As_Array
    'Possibility 2
    Ppt1.PptApp.Run Macroname_As_String, Parameters_As_Array 
    'Possibility 3
    PptApp.Run "'" & Ppt1.name & "'!" & Macroname_As_String, Parameters_As_Array
    'Possibility 4
    PptApp.Run Module_Name.Macroname_As_String, Parameters_As_Array
    'Possibility 5
    PptApp.Run "'" & Ppt1.name & "'!" & Module_Name.Macroname_As_String, Parameters_As_Array
0
votes

I found the answer here, where "UpdateOLELinks" is the name of the PowerPoint sub and the option to compile live as you type has not been disabled (it's enabled by default): https://www.ozgrid.com/forum/forum/other-software-applications/excel-and-or-powerpoint-help/26816-open-ppt-and-run-a-pre-written-macro

from ASHOK_SHARMA02:

Dim PPApp As PowerPoint.Application
Set PPApp = CreateObject("PowerPoint.Application")

PPApp.AddIns.Application.Run ("UpdateOLELinks"), ""

It worked for me after trying loads of possible solutions.

[edit] Actually it broke again when running the PPT from the VBA. Reason is VBA module has not yet been activated, so something like PPT doesn't know it exists (crazy huh?). So, add line 

PPApp.VBE.ActiveVBProject.VBComponents.Item("Module1").Activate
0
votes

There are two issues (which seem unique to PowerPoint), parameters are required and the macro name must be fully qualified.

When qualifying the macro, don't use single quotes as you would for Excel. Instead, just use the following, even if the filename has spaces:

PptApp.Run Ppt1.Name & "!Module1.UpdateOLELinks"

The error will also arise if the parameters being passed don't match the parameters of the macro. Ensure the macro has a defined parameter to receive (of matching type), even if it doesn't use it.

0
votes

This has been asked long before but still if anyone needs an answer, the follwoing worked for me.

    Dim objPP As Object
    Dim objPPFile As Object
    Set objPP = CreateObject("PowerPoint.Application")
    objPP.Visible = True
    Set objPPFile = objPP.Presentations.Open(PptPath)
    Application.EnableEvents = False
    '           "filename             !Module1.macro_name"
    objPP.Run "post_processing_V2.pptm!Module1.code"
    objPPFile.Close
    objPP.Quit
    Set objPPFile = Nothing
    Set objPP = Nothing