Calling VBA functions with varying parameter counts from Powershell

Question

I'm trying to make a Powershell script that can call any Excel macro. This works, as long as I know the amount of parameters beforehand. If I try to make this generic by using all 30 of the Microsoft.Office.Interop.Excel.Application.Run method parameters, I run into the following error message: Exception calling "Run" with "31" argument(s): "Number of parameters specified does not match the expected number."

Here's my attempt at this so far, starting with the contents of the .ps1 file:

param (
    [Parameter()][ValidateNotNullOrEmpty()][string]$excel_file_location,
    [Parameter()][ValidateNotNullOrEmpty()][string]$macro_name,
    [Parameter()][object[]]$macro_parameters
)

$par = (,$null) * 30

if($macro_parameters) {
    for ($i = 0; $i -lt $macro_parameters.Count -and $i -lt $par.Count; $i++) {
        $par[$i] = $macro_parameters[$i]
    }
}

$excel_app = New-Object -ComObject Excel.Application
$excel_app.Workbooks.Open($excel_file_location)
$excel_app.Run($macro_name, $par[0], $par[1], $par[2], $par[3], $par[4], $par[5], $par[6], $par[7], $par[8], $par[9], $par[10], $par[11], $par[12], $par[13], $par[14], $par[15], $par[16], $par[17], $par[18], $par[19], $par[20], $par[21], $par[22], $par[23], $par[24], $par[25], $par[26], $par[27], $par[28], $par[29])
$excel_app.Quit()

The Excel VBA file for testing purposes is a blank Excel 2016 .xlsm file with this macro:

Sub DoTheThing(x As String)
    Cells.Select
    Selection.Style = "Good"
    Range("A1").Select
    ActiveCell.FormulaR1C1 = x
    ActiveWorkbook.SaveAs Filename:="C:\Temp\macrotest2.xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

The command that results in the error (where ExecuteExcelVBA.ps1 and macrotest.xlsm are the described files above):

ExecuteExcelVBA.ps1 macrotest.xlsm DoTheThing (,"A1 value")

If I change the $excel_app.Run line to remove all the parameters after $par[0], it works.

Can I change my $par array values to some default that will have Excel ignore those parameters?

Comments on inefficiencies are welcome too.

AdminOfThings · Accepted Answer · 2019-05-14 15:00:28Z

2

You can use a combination of the -Replace and -Join operators to create a comma-space (,) delimited string using the elements of your $macro_parameters array. The resulting string can be concatenated with the .Run() method call command. The final string ($macro_run) can be called using Invoke-Expression. Note the use of single quote pairs during the $macro_run assignment to prevent the variables from being expanded.

param (
    [Parameter()][ValidateNotNullOrEmpty()][string]$excel_file_location,
    [Parameter()][ValidateNotNullOrEmpty()][string]$macro_name,
    [Parameter()][object[]]$macro_parameters
)
$par = $macro_parameters -replace ".*",'''$&' -join ", "

$excel_app = New-Object -ComObject Excel.Application
$excel_app.Workbooks.Open($excel_file_location)
$macro_run = '$excel_app.Run($macro_name, ' + $par + ')'
Invoke-Expression $macro_run
$excel_app.Quit()

edited May 14, 2019 at 15:00

answered May 14, 2019 at 12:34

AdminOfThings

25.2k4 gold badges22 silver badges30 bronze badges

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

4 Comments

Twon-ha Over a year ago

That doesn't throw up an error, but if I give two parameters to $macro_parameters, it just gives both in a joined string to the x parameter in the VBA.

Twon-ha Over a year ago

And if the VBA gets another parameter, it blows up again because it's only been given 1 argument instead of 2. Unless you're suggesting parsing the joined string in the VBA, which isn't an option here.

AdminOfThings Over a year ago

@Twon-ha I have made an update. Please let me know if that helps things. People will have issues with this code because Invoke-Expression is never a favorable command.

Twon-ha Over a year ago

Ooh, an eval. Still, better than my workaround of using a switch with a Run call for every parameter count.

Collectives™ on Stack Overflow

Calling VBA functions with varying parameter counts from Powershell

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related