Search code examples
compowershellms-officeoffice-interop

Looking up the value for MS Office Interop constants rather than hard coding them

Using PowerShell, it is easy enough to create, say, an instance of the Excel Application class and start manipulating it:

$app = New-Object -ComObject "Excel.Application"

However, if I need to use the constants like xlDoubleQuote or xlDelimited - it seems like I am forced to hard code them. I would really like to be able to do something like:

$constants = New-Object -ComObject "Excel.Constants"
$constants.xlDoubleQuote

And see that it would return the value of 1. Unfortunately I can't create an instance of an enumeration, and there doesn't seem to be a way to reference it like you would a normal .NET class library:

[Excel.Constants]::xlDoubleQuote

Is there some way to dynamically import that enumeration into PowerShell? Maybe through the managed libraries rather than COM?

Solution

  • Keith already gave you the answer, here's another option. You can use tab completion on the $xlConstants object to get the constants:

    $xl = New-Object -ComObject Excel.Application
$constants = $xl.gettype().assembly.getexportedtypes() | where-object {$_.IsEnum -and $_.name -eq 'constants'}

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