Run Powerpoint sub from Excel VBA

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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