How can a Powershell function specify a ComObject parameter type?

Question

Let's say that I'm trying to write a Powershell function that prints a result set to an Excel worksheet, like this:

function Write-ToWorksheet {
  param (
    [Parameter( Position = 0, Mandatory = $true )]
    [MyLibrary.MyCustomResultType[]]
    $ResultSet,

    [Parameter( Position = 1, Mandatory = $true )]
    [Excel.Worksheet]
    $Worksheet
  )
  # ... Implementation goes here ...
}

And let's say that I'm calling it in a way something like this:

$excel = New-Object -ComObject Excel.Application
$wb = $excel.Workbooks.Add()

$results = Get-MyResults # Never mind what this does.

Write-ToWorksheet -ResultSet $results -Worksheet $wb.Sheets[ 1 ]

And this code will almost work, except that it chokes on my type specification of [Excel.Worksheet].

I realize that it is not necessary to specify the parameter type, and that the code will work just fine without it, as this answer points out.

But to please my inner pedant, is there any way to constrain the parameter type using a reference to a COM object type like Excel.Worksheet?

user_3938951 · Answer

The reason that PowerShell is complaining about your Excel.Worksheet type is because it's not the name of the true .NET class/interface.

The parameter type you'd need to specify is Microsoft.Office.Interop.Excel.Worksheet instead (once the Excel interop assembly has been loaded, either directly via Add-Type or after the call to New-Object -ComObject Excel.Application as that will load the desired library too)

With that said, I don't believe this will work as intended because of the way that PowerShell handles COM objects by creating a transparent COM adapter layer between the true type of the variable exposed in PowerShell.

Interestingly there appear to be differences in the way that PowerShell handles parameter conversions when supplying them via Named arguments vs Positional arguments as can be seen with my demo code below:

function Get-WorksheetName {
  param (
    [Parameter( Position = 1, Mandatory = $true )]
    [Microsoft.Office.Interop.Excel.Worksheet]
    $Worksheet
  )

  return $Worksheet.Name
}

Calling the function using named arguments fails:

Whereas calling the function via positional arguments works as expected:

If positional arguments aren't something you'd like to use, then another alternative would be to drop the parameter type constraint and instead check the type using the ValidateScript attribute instead. This still ensures type safety:

function Get-WorksheetName {
  param (
    [Parameter(Position = 1, Mandatory = $true)]
    [ValidateScript({$_ -is [Microsoft.Office.Interop.Excel.Worksheet]})]
    $Worksheet
  )

  return $Worksheet.Name
}

Passing a different type of object would result in this:

How can a Powershell function specify a ComObject parameter type?

1 Answers