Error on type conversion in Powershell EXCEL Com object

Question

The following code:

$xl = New-Object -ComObject Excel.Application
$constants = $xl.gettype().assembly.getexportedtypes() | GM

where-object {$_.IsEnum -and $_.name -eq 'constants'}

$pso = new-object psobject
[enum]::getNames($constants) | foreach { $pso | Add-Member -MemberType NoteProperty $_ ($constants::$_) }
$xlConstants = $pso

Fails in the [enum]::getNames with the ff. message from Powershell 5.1 ISE:

Cannot convert argument "enumType", with value: "System.Object[]", for "GetNames" to type "System.Type": 
"Cannot convert the "System.Object[]" value of type "System.Object[]" to type "System.Type"."
At line:9 char:1

Would be grateful for some guidance.

The code was copied from a 2010 answer to a post, which wanted to extract the Excel Enum constants.

Answer 1

There's an extraneous GM (Get-Member) call in your code, and the Where-Object call - which should be where GM is - is disconnected from the pipeline above (which makes it a no-op).

$constants is therefore an array of objects (output by Get-Member), and passing an array to [enum]::GetNames() fails with the error you saw.

Find a corrected version of your code below, but your problem can be solved more simply, combining the solutions from the post your code came from as shown in this answer.

---

Here's a corrected version of your code that also shows you a faster PSv5+ solution for creating the custom object whose properties are named for the enumeration values' symbolic names and whose property values are the enumeration values themselves.

As the linked simpler solution shows, this isn't really necessary, however.

# Get the [enum]-derived type named 'Constants' from among
# the types that the Excel interop assembly exports.
$xl = New-Object -ComObject Excel.Application
$constantsType = $xl.GetType().Assembly.GetExportedTypes() | 
  Where-Object { $_.IsEnum -and $_.Name -eq 'constants' }

# Construct a custom object that reflects the enum type's
# enumeration values as properties.
$xlConstants = New-Object pscustomobject
[enum]::GetNames($constantsType).ForEach({
  $xlConstants.psobject.properties.Add([psnoteproperty]::new($_, $constantsType::$_)) 
})

• If you'd rather use the raw [int] values as the property values, use [psnoteproperty]::new($_, $constantsType::$_.value__) instead.

• .psobject.properties provides access to any object's properties, and the .Add() method allows creating properties. [psnoteproperty]::new() creates a note property (shown as type NoteProperty by Get-Member), i.e., a property with a static value; the first argument is the property name, and the 2nd the property value, which can be of any type.