Menu
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?
241
To assign a macro that you pass arguments to a button, shape, image, or any object, you first right-click that object and click Assign Macro and then type the name of the macro and the argument, following the pattern described in the above examples, and then click OK.
Open Excel Workbook, Run Macro, Then Close Self explanatory by reading the comments, but just make sure you change the File path and Macro name. Pushing the green run button up the top, should now load your Excel File, run the macro, wait for it finish, close Excel and display a popup box, finally close PowerShell.
To begin single stepping while a macro is running, press CTRL+BREAK. To begin single stepping at a specific point in your macro, you can add the SingleStep macro action to your macro at the point where you want single stepping to begin.
In this article we'll show how to read and write data from Excel worksheets directly from PowerShell scripts. You can use Excel along with PowerShell to inventory and generate various reports on computers, servers, infrastructure, Active Directory, etc.
You can run a macro with arguments like this:
$excel.Run('CreateChart', 'arg1', 'arg2', ...)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
