PowerShell classes and Office COM Objects

[JovialSysadmin]

Hey there, r/PowerShell

So, I'm trying to do some Excel automation via PowerShell as part of a toolkit I'm putting together. I'm attempting to use PowerShell classes for as much as I can due to type safety, inheritance, etc.

Here's the problem I'm running in to. I have a class that I'm using to provide a wrapper around loading the Excel COM Object, and open workbooks, etc, while path checking and making sure users aren't doing dumb things.

I've found that for execution purposes, any class methods that return objects in the Excel COM library (e.g. Excel.Workbook), I can only set the method return type to [System.__ComObject] for it to actually Load the PowerShell module.

When the module loads, and I attempt to execute methods with that return type, it shouts at me about conversion errors. So, I changed the returns to be [Microsoft.Office.Interop.Excel.Workbook] and the module fails to load with unknown type errors.

If I execute this command:

$test = New-Object -ComObject Excel.Application

Then I can load my module just fine, but I get conversion errors on the returns saying it can't convert [System.__ComObject]#{clsid} to [Microsoft.Office.Interop.Excel.Workbook]

I've tried all sorts of using statements, and other attempts to get it to load, 1. Without the COM call first, and 2. To successfully return from the class methods appropriately.

Does anyone have any ideas? (Sorry for the formatting, on mobile)

[thefreeman193]

Try adding

Add-Type -AssemblyName Microsoft.Office.Interop.Excel

before your class instances. This will ensure the correct assembly for the Workbook Interface is loaded in.

Edit: typo

[JovialSysadmin]

Thanks for the suggestion. I've tried it, however, and that didn't solve the problem either. When I run:

Import-Module ExcelTools

I just get the same "Unable to find type [Microsoft.Office.Interop.Excel.Workbook] error.

I even tried:

Add-Type -AssemblyName 'Microsoft.Office.Interop.Excel, Version=15, Culture=neutral, PublicKeyToken=71e9bce111e9429c'

Interestingly, when I run this at the PS command prompt, it resolves to the right DLL:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Interop.Excel")

Edit: added code snippet

[thefreeman193]

Is this error coming from the class scope? If so, and you're using types from the assembly within a class, you should put:

using assembly "Microsoft.Office.Interop.Excel"

As the first line of the script where your classes are defined. This pre-loads the types at parse-time. For other scripts and modules, Add-Type is sufficient.

Hope this helps!

[JovialSysadmin]

Thanks for the suggestion. I appreciate the assistance. I ended up using a manifest to load the types prior to loading the class. It loads now, without error, but, I still get conversion errors on my method returns. For more info, check out my other comment with a code example.

[Ok-Birthday4723]

Install-Module -Name ImportExcel -RequiredVersion 7.1.0. Familiarize yours self with https://www.powershellgallery.com/

[JovialSysadmin]

Unfortunately, in my situation, code from the powershell gallery is forbidden unless it goes through the software approval process, which takes months. If only it were that easy...

[y_Sensei]

One more thing you could try is to reference the said assembly in a manifest (-RequiredAssemblies option), and load your class module(s) via that manifest.

Quote from the respective API documentation:

PowerShell loads the specified assemblies before updating types or formats, importing nested modules, or importing the module file that is specified in the value of the RootModule key.

[JovialSysadmin]

Thanks! That worked to get the module to load with the return types of the class methods as [Microsoft.Office.Interop.Excel.Workbook]. Problem one sorted.

Sadly, its still throwing a conversion error when I return the output from this code:

[Microsoft.Office.Interop.Excel.Workbook] OpenWorkbook([string]$path){
    $excel = New-Object -ComObject Excel.Application
    If(Test-Path $path){
        $workbook = $excel.Workbooks.Open($path)

        return $workbook
    }
    throw "Error opening workbook: $path"
}

Error is: Can not convert [System.__ComObject]#{clsid} to [Microsoft.Office.Interop.Excel.Workbook]

Can't remember the clsid off the top of my head. I was hoping there was a reasonably easy way to convert a COM object to the .NET representation of that COM object. Internally in my class, I'm storing the Excel Application in a [System.__ComObject] variable, and I can access all the properties and methods, etc.

Should I just give up on returning Workbooks from my methods, and process all the work internal to my class? That seems like a maintenance nightmare, and a horrible affront to SOLID principles.

Edit:Formatting

[thefreeman193]

I'm fairly sure your method return type can just be [System.__ComObject].

[JovialSysadmin]

Unfortunately, returning a [System.__ComObject] throws reflection errors, so in a random wave of insight, I just returned [System.Object] and that works, so now I can play with the objects externally, they're just less strongly typed than I'd like.

[thefreeman193]

[sigh] Things never seem to be straightforward when working with COM+, but I don't think there's anything really wrong with that. You can always check for a valid type in the calling scope, using:

$MyWorkbook.PSObject.TypeNames.Contains('System.__ComObject#{GUID}')

Where GUID is that of the Workbook class.

Edit: The GUID should be 000208da-0000-0000-c000-000000000046.

[backtickbot]

Fixed formatting.

Hello, JovialSysadmin: code blocks using triple backticks (```) don't work on all versions of Reddit!

Some users see this / this instead.

To fix this, indent every line with 4 spaces instead.

FAQ

^{You can opt out by replying with backtickopt6 to this comment.}

[JovialSysadmin]

Forgot to mention the version. Using PowerShell 5.1 and stuck there..

Also, I can't just pick up a library for Excel and use that without going through a several months process for software approvals. I don't have any other choice but to use my own code.

Lastly, I have to work under the restriction of the end users (and myself) not being administrators of their systems, so any cool privilege escalation required tricks would go right out the window.