8

Trying to string the title together was enough of a challenge...

I am trying to run some PowerPoint macros from PowerShell. I have gotten quite good at running macros from Powershell for Excel. When I run the macros on Excel, the Run() method from the COM object will take a variety of arguments, depending if the macro has any parameters. However on the other hand, the PowerPoint Run() method expects parameters, and I cannot work out how to pass them.

My macro is expecting one string to be passed through, I've googled profusely and come up short. I always get this error:

Error:

type must not be ByRef

I have put together a very basic PoC for PowerPoint in PowerShell:

Code:

# PowerPoint test
Add-type -AssemblyName office
$PowerPoint = New-Object -comobject PowerPoint.Application

$PowerPoint.Visible = [Microsoft.Office.Core.MsoTriState]::msoTrue

$presentation2 = $PowerPoint.Presentations.open("C:\macros.pptm")
$presentation = $PowerPoint.Presentations.open("C:\Test For Macros.pptx")

$PowerPoint.run("macros.pptm!IAM",[ref]"Feb")
$presentation.save()
$presentation.close()

$presentation2.close()
$PowerPoint.quit()

# extra clean up omitted

The macro itself just moves some text across boxes, it works fine when run from PowerPoint.

Requirement:

I now want to automate this across multiple files and slides.

Documentation on the PowerPoint COM object Run method, showing the requirement for two parameters.

Will Webb
  • 795
  • 6
  • 20
Chris
  • 304
  • 7
  • 21
  • Can you post the macro in the pptm (at least the declaration of the sub)? Can you tell us what kind of references (Tools > References) are included in it? Can you test that (1) can you show a message box at the beginning of the macro (2) Another macro with the same types of arguments can be run from the same file (3) Another macro from the same file without arguments can be run? (4) Any macro from pptm files can be run from PowerShell on your computer? – z32a7ul Apr 06 '17 at 08:27
  • I asked these because my hypothesis is that the error does not occur when parameters are handed over to the Macro but later, and an exception is thrown back and caught finally by PowerShell. – z32a7ul Apr 06 '17 at 08:33

2 Answers2

4

Great question and not a lot of examples online as you say. I managed to strip your example down even further and successfully pass some text to a MsgBox in a PowerPoint Macro without really changing what you had.

The Macro in the file PowerShellTest.pptm saved in C:\Temp

Sub DisplayMessage(myText As String)
  MsgBox myText
End Sub

The PowerShell script:

# PowerPoint test
Add-type -AssemblyName office
$PowerPoint = New-Object -comobject PowerPoint.Application

$PowerPoint.Visible = [Microsoft.Office.Core.MsoTriState]::msoTrue

$presentation = $PowerPoint.Presentations.open("C:\Temp\PowerShellTest.pptm")

$PowerPoint.run("PowerShellTest.pptm!DisplayMessage",[ref]"Feb")

The Run method documentation link you provided mentions that the module name may be included so this worked for me too:

$PowerPoint.run("PowerShellTest.pptm!Module1.DisplayMessage",[ref]"Feb")
Jamie Garroch - MVP
  • 2,839
  • 2
  • 16
  • 24
  • Thanks for the simpler example to try, I've tried running your code verbatim and I get exactly the same output, so there must be something else at play my end. – Chris Feb 23 '16 at 16:15
  • 1
    Hmmm. Maybe some of the trust centre settings? I din't sing my macro project (as I usually do) so I wouldn't have been bypassing some of the more verbose MSFT issues. – Jamie Garroch - MVP Feb 24 '16 at 17:11
  • Would this be different to the trust centre in Excel? As I'm able to run macros fine through PowerShell to Excel. Just trying to isolate the problem. – Chris Feb 29 '16 at 11:47
  • 1
    Yes. Trust Centre settings are application dependent e.g. different for each Office app. Not sure if this is the issue of course. – Jamie Garroch - MVP Mar 02 '16 at 16:44
1

Maybe try removing the [ref] in the line of code below or try changing it to [val]

$PowerPoint.run("macros.pptm!IAM",[ref]"Feb")

So either:

$PowerPoint.run("macros.pptm!IAM","Feb")

or:

$PowerPoint.run("macros.pptm!IAM",[val]"Feb")

And then also make sure that the PowerPoint macro is expecting the variable to be passed ByVal. For example something like this:

Sub IAM(ByVal sMonthName As String)
J. Garth
  • 783
  • 6
  • 10
  • Thank you for the suggestions! So, option 1 (remove [ref]) gives the error: "type must not be ByRef" and option 2 ([val]) gives: "Unable to find type [val]. Make sure that the assembly that contains this type is loaded." – Chris Apr 04 '17 at 13:36
  • 1
    Maybe the PowerPoint macro is expecting the argument to be passed byRef? Have you checked the macro signature to make sure that it is not expecting the argument to be passed byRef? – J. Garth Apr 04 '17 at 14:10
  • I added ByVal to the macro as well like you suggested. All it does is take one String and put that in a MsgBox, exactly like Jamie's suggestion in the first answer. – Chris Apr 04 '17 at 14:14
  • 1
    Maybe this link will be helpful. https://www.vistax64.com/powershell/2595-passing-variable-reference.html – J. Garth Apr 04 '17 at 14:25