Are there significant differences between using COM directly or using an interop in PowerShell?

I am automating the creation of Excel workbook with PowerShell scripts.

I can create an Excel instance with either

add-type -assembly Microsoft.Office.Interop.Excel
$xls = new-object Microsoft.Office.Interop.Excel.ApplicationClass

or

$xls = new-object -com application.excel

I am wondering if one method is preferred over the other and/or in which uses cases I should go with the former or latter variant.

tl;dr:

• For code running in Windows PowerShell on machines where the Excel PIA is installed (the Primary Interop Assembly for Excel's COM API, which is an optional component of Office installations), the two approaches in your question are equivalent: the New-Object -ComObject Application.Excel approach then uses the PIA's types (e.g. [Microsoft.Office.Interop.Excel.ApplicationClass]) implicitly.

• On machines without the Excel PIA and, independently and fundamentally, for code running in PowerShell (Core) 7 as of PowerShell 7.5.x, only the New-Object -ComObject Application.Excel approach works.

  • It then uses COM's IDispatch interface rather than the PIA in order to dynamically discover and access the members of the Excel object model, which notably means that then-unavailable friendly PIA enum types such as [Microsoft.Office.Interop.Excel.XlWindowState] are expressed as numbers rather than as symbolic constants.

• Given the above, New-Object -ComObject Application.Excel is the more robust choice (assuming that at least Excel itself is installed), but, importantly any code that must (also) run in non-PIA contexts must avoid explicit references to the PIA's .NET types ([Microsoft.Office.Interop.Excel.*]).

Background information

• In Windows PowerShell (the legacy, ships-with-Windows, Windows-only edition of PowerShell whose latest and last version is 5.1), the two methods are, in fact, equivalent, provided that the Excel PIA (Primary [COM] Interop Assembly) is present on a given machine.

  • That is, New-Object -ComObject Application.Excel implicitly loads the Excel PIA, Microsoft.Office.Interop.Excel, from the GAC, if present, and outputs a [Microsoft.Office.Interop.Excel.ApplicationClass] instance.

  • Note:

    • The Microsoft.Office.Interop.Excel.ApplicationClass type is documented as "Reserved for internal use", presumably because it is meant to accessed via a .NET interface from the same PIA, so from that perspective the
      New-Object -ComObject Application.Excel approach may be preferable, perhaps also to make it obvious that a COM component is involved. If you also need to run in contexts where presence / access to the PIA can not be guaranteed, said approach is indeed your only option - though you'll then have to avoid references to any of the PIA's .NET types: see next section.

    • That said, given that said type is technically public and given that PowerShell implicitly also exposes members from a type's explicit interface implementations as if they were direct type members, the New-Object Microsoft.Office.Interop.Excel.ApplicationClass approach is probably safe, too.

• By contrast, in PowerShell (Core) 7, neither approach works:

  • PowerShell 7 doesn't consult the GAC-installed PIAs anymore and cannot locate them by name only, which implies:

    • Add-Type -AssemblyName Microsoft.Office.Interop.Excel doesn't work.

    • New-Object -ComObject Application.Excel outputs an object that implements COM's IDispatch interface which allows PowerShell to dynamically discover the members of the Excel object model instead of accessing them via .NET types.

    • While locating the PIA yourself and loading it by full path, with Add-Type -LiteralPath, is possible in principle, it additionally requires manually loading all of its dependencies first - see GitHub issue #2462.

      • If you clear that hurdle, you'll have to work with the PIA types directly (New-Object Microsoft.Office.Interop.Excel.ApplicationClass), because New-Object -ComObject Application.Excel will not use the explicitly loaded PIA.
        Additionally, it seems that all nested objects (stored in properties) of [Microsoft.Office.Interop.Excel.ApplicationClass] instances do not honor the PIA types and are again reported as IDispatch COM objects.

    • Per GitHub issue #11402, it is a change in .NET (relative to .NET Framework) that causes PIAs to no longer get consulted; presumably, New-Object would have to be extended to compensate for the .NET change, though it is unclear to me whether that would also solve the problem with the nested objects.

It follows from the above:

• Using New-Object -ComObject Application.Excel does not involve PIAs in the following circumstances:

  • never in PowerShell (Core) 7.

  • in Windows PowerShell only on those machines where the Excel PIA happens not to be installed.

• Therefore, unless your script needs to run in Windows PowerShell only and you can guarantee the presence of the Excel PIA on all machines it must run on, do NOT use PIA-based code.

  • This means:

    • using New-Object -ComObject Excel.Application to instantiate Excel.
    • avoiding any references to PIA types ([[Microsoft.Office.Interop.Excel.*]) in your code.

  • Adding the -Strict switch to the New-Object -ComObject Application.Excel call alerts you if a PIA is implicitly being used on a given machine, in the form of a non-terminating error, but it is still up to you to avoid explicit references to PIA types such as [Microsoft.Office.Interop.Excel.ApplicationClass] in your code if you want the code to also work in PowerShell 7 or on machines without PIAs.

    • Given that there is no way to opt out of PIA use in Windows PowerShell, the safest way to develop PIA-free is to either use a machine that doesn't have the PIAs installed or to (temporarily) remove them from the GAC - see this answer for how to locate them.

    • Alternatively, if you need to support PowerShell 7 too, develop there (though you'll still need to test in Windows PowerShell too to make sure your scripts truly work in both editions).

As for why you might consider use of the PIA:

If the above limitations do not apply to you, i.e. if your code must only run in Windows PowerShell and only on machines where the PIA is installed, PIA use has the following - limited - advantages:

• Explicit references to PIA types such as [Microsoft.Office.Interop.Excel.XlWindowState] can make your code more readable, including the ability to use enumeration values as strings in comparisons and assignments, e.g. $xl.WindowState -eq 'xlNormal' rather than the more abstract $xl.WindowState -eq -4143

• Printing objects to the display expresses property types in terms of PIA types rather than the abstract type name of the generic COM runtime wrapper, System.__ComObject^[1]

  • However, note that Get-Member shows meaningful type names even without the PIA present / even in PowerShell 7.

• Knowing the PIA type names makes it easier to look up the relevant online documentation, e.g. for Microsoft.Office.Interop.Excel.XlWindowState

^{[1] Curiously, I get a mix of PIA type names and System.__ComObject in Windows PowerShell with the PIA installed - I'm unclear on the cause; conceivably, there could be a version mismatch between Excel and the PIA on my machine.}