none
Run Macro with dynamic parameter RRS feed

  • Question

  • I'm trying to execute some macros though my powershell in a dynamic way. I receive a dictionary with macro names and parameters and try to call the macros and execute it. 

    It is giving me the following error:

    System.Management.Automation.MethodInvocationException: Exception calling "Run" with "4" argument(s): "Number of parameters specified does not match the expected number." ---> System.Reflection.TargetParameterCountException: Number of parameters specified does not match the expected number.


    This is the code.

    function Quote-Param([object[]] $param) {
    	#put quotes into the parameters unless they are numeric
    	[decimal] $number = 01
    	foreach ($p in $param) {
    		if ([decimal]::TryParse($p, [ref]$number)) {$p} else {('"{0}"' -f $p) }
    	}
    
    }
    
    $xl = new-Object -comObject excel.application
    #Show Excel
    $xl.visible = $Visible
    $xl.DisplayAlerts = $false
    # Open Excel file	
    $workbook = $xl.Workbooks.Open("C:\Excel.xlsm",0,0,5,"password")
     
    
    $ListMacros = @{"Macro1" = "Param1#Param2#Param3"	;"Macro2"=""	;"Macro3"=""}
    
    
    Foreach ($key in $ListMacros.GetEnumerator()) {
    
        $arguments = @(Quote-Param $key.Name)
        $arguments += Quote-Param $key.Value.Split("#")
        $arguments = $arguments -join ','  
    
        $xl.run.Invoke($arguments)
    
    }

    If in the line 

    $xl.run.Invoke($arguments)

    I pass:

    $xl.run.Invoke("Macro1", "Param1", "Param2" , "Param3")

    It works.

    Do you have any experience on this?

    Thank you in advance.

    Thursday, May 16, 2019 11:44 AM

All replies

  • Just run the macro and supply the arguments.

    $xl.Run(<macro name>, <arg1>,<arg2>,<arg3> … )

    $xl.run("Macro1", "Param1", "Param2" , "Param3")

    You cannot pass an array to the "Run" method.

    See: https://docs.microsoft.com/en-us/office/vba/api/excel.application.run


    \_(ツ)_/

    Thursday, May 16, 2019 12:05 PM
    Moderator
  • Like this:

    $macros = @{
        Macro1 = @('Param1','Param2','Param3')
        Macro2 = @('Param1', 'Param2')
        Macro3 = @()
    }
    foreach($key in $macros){
        switch ($macros[$key].Count){
            0 { $xl.Run($key) }
            1 { $xl.Run($key,$macros[$key][0])}
            2 { $xl.Run($key,$macros[$key][0],$macros[$key][1])}
            3 { $xl.Run($key,$macros[$key][0],$macros[$key][1],$macros[$key][2]) }
        }
    }
    


    \_(ツ)_/

    Thursday, May 16, 2019 12:12 PM
    Moderator
  • To use the "Invoke" method we would do this:

    $macros = @(
        @('Macro1','Param1','Param2','Param3'),
        @('Macro2'),
        @('Macro3','Param1')
    )
    foreach($macro in $macros){
        $xl.Run.Invoke($macro)
    }


    \_(ツ)_/

    Thursday, May 16, 2019 12:18 PM
    Moderator
  • Hi,
    Was your issue resolved? 
    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.
    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.
    If no, please reply and tell us the current situation in order to provide further help.
    Best Regards,
    Lee

    Just do it.

    Monday, May 20, 2019 6:43 AM
    Moderator
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee


    Just do it.

    Monday, June 3, 2019 7:41 AM
    Moderator