Missing EXCEL [Enum]s in PS7

PowerShell Help
1

Hello, I’m sorry to be a pain, but I am discovering another “won’t work in PS 7, but works in PS 5.1 ISE”. I have found it convenient for my testing to have a .ps1 file sitting by so I can easily copy these short “2-liners”:

[Enum]::getvalues([Microsoft.Office.Interop.Excel.XLVAlign]) |
select-object @{n="Name";e={"$_"}},value__

[Enum]::getvalues([Microsoft.Office.Interop.Excel.XLLineStyle]) |
select-object @{n="Name";e={"$_"}},value__
However, I run into errors with all of them in PS7:
InvalidOperation: Unable to find type [Microsoft.Office.Interop,Excel.xxxx]
where 'xxxx' is one of those Excel constant names, e,g., XLVAlign, XLLineStyle, etc..
These lines of code work perfectly in PS 5.1 ISE. Don't understand why they don't in PS7.
Would be grateful for any guidance, tips.
Many thanks.
 
2

Hi Ramon,

According to https://github.com/PowerShell/PowerShell/issues/11281 this is expected behaviour, due to PowerShell 7 being based on .Net Core rather than .Net Framework.

On a positive note, I can get your code to work by adding the following line before your code:

Add-Type -Path C:\WINDOWS\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll

Regards,

Stuart.

3

Thank you Mr Stuart Squibb for your response. I tried the line just before the Enums and got the message:

Resource Unavailable: Program 'Microsoft.Office.Interop.Excel.dll' failed to run.
No application is associated with the specified file for this operation. At line: 1 char: 1
+C:\WINDOWS\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0

My PC is running Windows 10 64-bit, and Powershell 7 (1.43.1), free edition. I also have Office 2019 which has EXCEL application with it. Would be grateful for any tips/guidance. Thanks.

4

Obviously your Excel version differs from the one Stuart uses. Find the similar file of your version on your harddisk and change the path accordingly.

5

Thanks very much Olaf – it now works! Just typing that long string was a struggle … Also, I am suddenly “losing” the last few characters when reading Powershell.org posts and replies. My laptop (15") screen is already maximized. Thanks Stuart!

6

Have you heard about a fancy technique called “Cut And Paste”? :wink:

Did you try to zoom? In most browsers you can do with <Strg><+> and <Strg><-> or with <Strg><MouseWheel> … or try to scroll. :wink:

7

Yes, I cut-and-pasted several times, but it seems I missed the “tail end” portion … anyway, finally did it the primitive way: retyped the whole thing (!) … I am testing my screen with respect to Powershell.org webesit … no, I did not zoom, but will try out you suggestions first … Much thanks again for all the help.

8

What is this magical “Cut and Paste” you speak of? This should be a blog.