Start Excel Macro with parameter from PowerShell

Question

I encountered a problem with the following PowerSehll code in order to start an Excel macro and pass parameters.

# start Excel
$excel = New-Object -comobject Excel.Application

#open file
$FilePath = 'C:\Users\{user}\Desktop\test\Macro.xlsm'
$workbook = $excel.Workbooks.Open($FilePath)

$excel.Visible = $true

#access the Application object and run a macro
$app = $excel.Application

$sourcepath = 'C:\Users\{user}\Desktop\test\FileIn.csv'
$destinationpath = 'C:\Users\{user}\Desktop\test\FileOut.xls'

$app.Run('FormatUmsatzliste',$sourcepath,$destinationpath) 
$app.Quit()

The code works fine and the macro gets executed and does also what it should. However, at the end I get the following error message and I don't know why.

As I understand it, PowerShell doesn't like the parameter. I hope you can help me.

PS L:\> C:\Data\Untitled1.ps1
Exception calling "Run" with "3" argument(s): "Exception from HRESULT: 0x800A9C68"
At C:\Data\Untitled1.ps1:17 char:1
+ $app.Run('FormatUmsatzliste',$sourcepath,$destinationpath)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : COMException

OverloadDefinitions System.Object Run(System.Object Macro, System.Object Arg1, System.Object Arg2, System.Object Arg3, System.Object Arg4, System.Object Arg5, System.Object Arg6, System.Object Arg7, System.Object Arg8, System.Object Arg9, System.Object Arg10, System.Object Arg11, System.Object Arg12, System.Object Arg13, System.Object Arg14, System.Object Arg15, System.Object Arg16, System.Object Arg17, System.Object Arg18, System.Object Arg19, System.Object Arg20, System.Object Arg21, System.Object Arg22, System.Object Arg23, System.Object Arg24, System.Object Arg25, System.Object Arg26, System.Object Arg27, System.Object Arg28, System.Object Arg29, System.Object Arg30) System.Object _Application.Run(System.Object Macro, System.Object Arg1, System.Object Arg2, System.Object Arg3, System.Object Arg4, System.Object Arg5, System.Object Arg6, System.Object Arg7, System.Object Arg8, System.Object Arg9, System.Object Arg10, System.Object Arg11, System.Object Arg12, System.Object Arg13, System.Object Arg14, System.Object Arg15, System.Object Arg16, System.Object Arg17, System.Object Arg18, System.Object Arg19, System.Object Arg20, System.Object Arg21, System.Object Arg22, System.Object Arg23, System.Object Arg24, System.Object Arg25, System.Object Arg26, System.Object Arg27, System.Object Arg28, System.Object Arg29, System.Object Arg30)

Best regards.

Hi Vesper, thank you very much this change solved the issue. — psychicebola
– psychicebola, Commented Jul 8, 2015 at 6:47
I've posted that as an answer so that the question won't appear to be unanswered anymore. — Vesper
– Vesper, Commented Jul 8, 2015 at 6:51

Vesper · Accepted Answer · 2015-07-08 06:51:25Z

2

The solution is do not use Workbook.close in your macro if you're starting it via Excel.Application COM object. This issue is described here in details: HRESULT 0x800A9C68 in Excel 2010. The solution is remove the call to Workbook.Close from the macro being called like this.

answered Jul 8, 2015 at 6:51

Vesper

18.8k6 gold badges41 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Start Excel Macro with parameter from PowerShell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related