Calling Excel macros from PowerShell with arguments

Question

Using Powershell it's rather easy to call Excel macro's from a script, for example with a script like this:

$excel = new-object -comobject excel.application
$excelFiles = Get-ChildItem -Path C:\fso -Include *.xls, *.xlsm -Recurse
Foreach($file in $excelFiles)
{
   $workbook = $excel.workbooks.open($file.fullname)
   $worksheet = $workbook.worksheets.item(1)
   $excel.Run("CreateChart")
   $workbook.save()
   $workbook.close()
}
$excel.quit()

However, I didn't manage to call a macro with some arguments. Is this possible or is the best way to write a config file that the macro will read when called?

Thank you for posting this example. I've been looking for a way to do this and finally figured it out thanks to this post. I know this is old but if you are still around, it looks like excel is run in the background. Any chance Excel can be made visible so I know it's running? — DanCue
– DanCue, Commented Sep 25, 2024 at 13:41
Set the Visible property to true with $excel.Visible = $true. — Bob Jansen
– Bob Jansen, Commented Sep 26, 2024 at 8:49

Ansgar Wiechers · Accepted Answer · 2013-10-23 09:31:53Z

11

You can run a macro with arguments like this:

$excel.Run('CreateChart', 'arg1', 'arg2', ...)

answered Oct 23, 2013 at 9:31

Ansgar Wiechers

201k27 gold badges286 silver badges363 bronze badges

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

1 Comment

Aaron C Over a year ago

I had to set the name on the macro as follows ThisWorksheet.CreateChart, so your code became $excel.Run('ThisWorkbook.CreateChart', 'arg1', 'arg2', ...)

Collectives™ on Stack Overflow

Calling Excel macros from PowerShell with arguments

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related