Missing EXCEL [Enum]s in PS7

Welcome Forums General PowerShell Q&A Missing EXCEL [Enum]s in PS7

Viewing 7 reply threads
  • Author
    Posts
    • #211917
      Participant
      Topics: 86
      Replies: 138
      Points: 676
      Rank: Major Contributor

      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.

       

      • This topic was modified 3 months, 1 week ago by Ramon TAN.
      • This topic was modified 3 months, 1 week ago by Ramon TAN.
    • #212022
      Participant
      Topics: 0
      Replies: 14
      Points: 151
      Rank: Participant

      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.

    • #212112
      Participant
      Topics: 86
      Replies: 138
      Points: 676
      Rank: Major Contributor

      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.

    • #212121
      Participant
      Topics: 5
      Replies: 2368
      Points: 5,986
      Helping Hand
      Rank: Community MVP

      …. I also have Office 2019 which has EXCEL application with it. Would be grateful for any tips/guidance. Thanks.

      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.

    • #212127
      Participant
      Topics: 86
      Replies: 138
      Points: 676
      Rank: Major Contributor

      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!

    • #212151
      Participant
      Topics: 5
      Replies: 2368
      Points: 5,986
      Helping Hand
      Rank: Community MVP

      Just typing that long string was a struggle …

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

      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!

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

    • #212175
      Participant
      Topics: 86
      Replies: 138
      Points: 676
      Rank: Major Contributor

      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.

    • #212211
      Participant
      Topics: 12
      Replies: 1642
      Points: 2,660
      Helping Hand
      Rank: Community Hero

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

Viewing 7 reply threads
  • The topic ‘Missing EXCEL [Enum]s in PS7’ is closed to new replies.