How to pass a COM object parameter to a PowerShell function?

Question

I have a PowerShell script to open several Excel spreadsheets and write to a cell in each spreadsheet. I'd like to put the code to open each spreadsheet, write to it, and save it in a function. Rather than creating a new instance of the Excel.Application COM object for every spreadsheet, I'd like to pass an instance to the function, so that the instance can be reused.

Here's my function:

function InsertBlurb
{
    param([Object[]]$xl, [string]$filename, [string]$blurb)
    $WorkBook = $xl.Workbooks.Open($filename)
    $WorkSheet = $WorkBook.Sheets.Item("Print Out")
    $WorkSheet.Cells.Item(12, "A") = $blurb
    $WorkBook.Save()
    $WorkBook.Close()
}

# Call looks like this
InsertBlurb($objExcel, $dsBook, $blurb)

When I try to run the function, I get an error:

You cannot call a method on a null-valued expression.
At T:\rclocher3\Excel-to-PDF2.ps1:32 char:35
+     $WorkBook = $xl.Workbooks.Open <<<< ($filename)
    + CategoryInfo          : InvalidOperation: (Open:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

How do I pass an instance of the COM object Excel.Application to a PowerShell function?

score 1 · Accepted Answer · answered Apr 06 '20 at 19:43

1

First off, your call should be:

InsertBlurb $objExcel $dsBook $blurb

Powershell functions are called with no parentheses and no commas.

IDK if you have $xl defined write in the function. Maybe, maybe not...leaning toward the latter, but not sure. You can leave off the type if the type is wrong. Hard to imagine it is an object array.

answered Apr 06 '20 at 19:43

Joseph Willcoxson

5,853
1
15
29

positional parameters wont work unless `[Parameter(Position=Number)]` is added when using Param. – Roque Sosa Apr 06 '20 at 19:57
According to Microsoft, "y default, all function parameters are positional. PowerShell assigns position numbers to parameters in the order in which the parameters are declared in the function. To disable this feature, set the value of the PositionalBinding argument of the CmdletBinding attribute to $False" This from https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_functions_advanced_parameters?view=powershell-7 – Joseph Willcoxson Apr 06 '20 at 20:27
Need to put some debug statements in your function to print out the parameters received in the function. Then after the call to Open, check the value of $Workbook. Then after next call check value of $Worksheet. Personally, I'd first write an equivalent function inside of the Excel VBA environment and make sure all the calls inside that environment worked. Maybe you're missing a parameter in one of your calls. IDK... – Joseph Willcoxson Apr 06 '20 at 20:43
your right, I believe this wasn't the case on previous powershell versions. thanks for the correction. – Roque Sosa Apr 06 '20 at 20:44
AFAIK... AFAIK... my experience has been it's been the case at least since Version 2. I think... Never ran into a problem before. – Joseph Willcoxson Apr 06 '20 at 20:45
I took out the type declaration, the parentheses, and the commas, and it works! – rclocher3 Apr 06 '20 at 20:49

How to pass a COM object parameter to a PowerShell function?

1 Answers1