5

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?

Improve this question

3 Answers 3

Reset to default
6

This is a little more flexible way to access it from PowerShell 7:

$original_pwd = (Get-Location).Path

cd  C:\Windows\assembly\

$interop_assemply_location = (Get-ChildItem -Recurse  Microsoft.Office.Interop.Outlook.dll).Directory

cd $interop_assemply_location 

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

cd "$original_pwd"

$Outlook = New-Object -comobject Outlook.Application

$namespace = $Outlook.GetNameSpace("MAPI")
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

if you want it to be more flexible then you need to use cd "$env:windir\assembly" instead of hard-coding the path
5

Based on Adrian's code and Phuclv's comment:

$AssemblyFile = (get-childitem $env:windir\assembly -Recurse Microsoft.Office.Interop.Excel.dll | Select-Object -first 1).FullName
Add-Type -Path $AssemblyFile
Improve this answer

1 Comment

Simple and works very well.
4

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.

Improve this answer

1 Comment

Thank you! I succeed with Add-Type -AssemblyName having been copied the DLL from C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\(my_version) to the script's directory. I don't like this method because of manual work, that depends on my particular interop version's directory path, and need to cd into my script's directory before calling Add-Type -AssemblyName.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.