Powershell run Excel macro with dynamic amount of parameters

Question

I'm trying to automate some different Excel files with a start macro, where the additionals parameters are stored in a job CSV file atm.

The problem is how to build the run command for Excel where you run the macro, with parameters like this, where "Start" is the name of the macro and in this example 3 additional parameters.

$excel.Run("Start","param1","param2","param3")

I have tried building the command and parameters as a string like I would if it was a normal cmd-let, and then invoke-expression, but the run command just read it as one string.

So how do I build the Run command with a dynamic number of parameters, without building some kind of switch that handles lets say 1-5 different parameters. that all calls $excel.run command with different amount of parameters, that's just a bit ugly and not very dynamic.

It may be that the call operator '&' would be helpful. blogs.msdn.microsoft.com/powershell/2011/06/03/… — lit
– lit, Commented Aug 27, 2018 at 12:31

Matt · Accepted Answer · 2018-08-27 13:35:41Z

Basically you are looking for a way to splat a method. That is only useful for cmdlets and functions, not methods. However PowerShell supports something called Dynamic Method Invocation which could work well here.

Consider this basic example comparing two strings. We supply .Compare() with an array which you could change as you need.

# Compare the two strings of mixed case
$strings = "F","f"
[string]::Compare.Invoke($strings)

# Compare the two strings of mixed case but set the ignore case flag
$strings = "F","f",$true
[string]::Compare.Invoke($strings)

Results for the above should be 1 then 0 as those strings only match when case is ignored.

Normally Compare() would return True or false. In this manner it just returns an integer where non-zero is for mismatched strings.

So lets try this out with your code.

$arguments = "Start","param1","param2","param3"
$excel.Run.Invoke($arguments)

I don't have an ideal setup to test this but it should work. Beware of return values might need to be handled differently via this approach. Test!

Thank you, Dynamic Method Invocation was excatly what I needed, since it allows me to dynamic build an arguments array, and i dont use return values.

Lucas Raphael Pianegonda · Accepted Answer · 2018-08-27 12:10:45Z

0

You can run a macro with arguments like this:

$excel.Run('Workbookobject.Macroname', 'param1', 'param2', 'param3', ...)

answered Aug 27, 2018 at 12:10

Lucas Raphael Pianegonda

1,18112 silver badges31 bronze badges

Collectives™ on Stack Overflow

Powershell run Excel macro with dynamic amount of parameters

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related