Search code examples
excelpowershellms-office

PowerShell 6 doesn't find Excel Interop. PowerShell 5.1 does

In order to use constants from the xlFileFormat enum, I used

[reflection.assembly]::LoadWithPartialName("Microsoft.Office.InterOp.Excel")

In the PowerShell 5.1 it works. Now I do transition to PowerShell 6. The same line issues the error message:

Exception calling "LoadWithPartialName" with "1" argument(s): "Could not load file or assembly 'Microsoft.Office.InterOp.Excel, Culture=neutral, PublicKeyToken=null'. Operation is not supported."

Calling Add-Type instead, I get an error too:

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

Add-Type : Cannot find path 'C:\transform\software\Microsoft.Office.Interop.Excel.dll' because it does not exist.

How can I load the Interop DLL installed with the Office?

Solution

  • In Windows PowerShell, which is built on (the full) .NET Framework, loading assemblies with [System.Reflection.Assembly]::LoadWithPartialName() or - preferably - Add-Type -AssemblyName looks for assemblies in the GAC (Global Assembly Cache), and seemingly in both:

    • the .NET Framework 4+ GAC, $env:WINDIR\Microsoft.Net\assembly

    • and also the older .NET Framework 3.x- GAC, $env:WINDIR\assembly

    Microsoft.Office.InterOp.Excel.dll is located in the .NET Framework 3.x- GAC (only), so Windows PowerShell is able to find it by the assembly's simple name (partial name), Microsoft.Office.InterOp.Excel.

    PowerShell [Core] is built on .NET Core, which itself has no GAC.

    In PowerShell [Core], Add-Type -AssemblyName:

    • first looks in the current directory,
    • then among the assemblies in $PSHOME, PowerShell Core's installation folder.
    • and, finally, in the .NET Framework 4+ GAC, but seemingly not the .NET Framework 3.x- GAC (as tested in .NET Core 3.1 / PowerShell [Core] 7.0, with the exception noted below) - presumably, because the assemblies there are assumed to be incompatible with .NET Core - even though not all of them technically are.

    Therefore,

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.Office.InterOp.Excel')

    doesn't find the assembly.

    Curiously, Add-Type -AssemblyName Microsoft.Office.InterOp.Excel finds it[1], but fails to load the actual types, which you can see by adding -PassThru.

    Generally, speaking:

    • You can load assemblies by (full) file path with Add-Type -LiteralPath.

      • Note that Add-Type not reporting an error is itself not enough to conclude that its types are usable:
        adding -PassThru is a quick way to verify that: normally, information about the types should be listed; otherwise, you'll see errors.

        Note that assemblies may be stub assemblies that themselves contain no types and instead forward types from different assemblies; as of PowerShell 7.0, -PassThru then produces no output rather than listing the forwarded types - see this GitHub issue.

    • If you place an assembly, say Foo.Bar.dll, in your script's folder, you can load it with
      Add-Type -LiteralPath $PSScriptRoot/Foo.Bar.dll

    [1] PowerShell [Core] seems to have its own logic for locating assemblies in the GAC: see method TryFindInGAC in CorePsAssemblyLoadContext.cs. However, the .NET 3.x- GAC, $env:WINDIR\assembly, appears not to be searched, so I have no explanation for why Microsoft.Office.InterOp.Excel is found - which is not also present in the .NET 4+ GAC - is found; any other .NET 3.x- GAC-only assemblies appear not to be found as expected.