Getting all the EXCEL enums

Welcome Forums General PowerShell Q&A Getting all the EXCEL enums

This topic contains 2 replies, has 2 voices, and was last updated by

 
Participant
5 months ago.

  • Author
    Posts
  • #146333

    Participant
    Topics: 73
    Replies: 119
    Points: 506
    Rank: Major Contributor

    I have the ff:

    $xl = New-Object -ComObject Excel.Application
    
    $xlEnum = New-Object -TypeName PSObject
    
    # get all Excel exported types of type Enum
    
    $xl.GetType().Assembly.GetExportedTypes() |
    
    Where-object {$_.BaseType -eq "System.enum"} | Out-gridview
    
    # -- the final intent is the ff:
    
    Sort-object -property Name -Descending | Out-gridview

    The goal is to get a listing of all the EXCEL enums for such things as cell color, height, width, etc.. (there are thousands).  I am however stucked at the Where-object.  It seems the attempt to filter only "System.Enum" BaseTypes is failing.  The property name 'BaseType' is of type BaseType, according to GM if I pipe the output of the preceding line to GM.  The question then is how do I compare to get a match of type BaseType?  I understand my code is implying a [string] type, but what is the correct way of expressing this, if possible?  Hope I've stated the problem clearly.  Thanks.

  • #146336

    Senior Moderator
    Topics: 8
    Replies: 1008
    Points: 3,275
    Helping Hand
    Rank: Community Hero

    Not sure whether I understood the question properly, but if you are facing with the filter, you should use below filters.

    $_.BaseType.Name -match "Enum"
    #or
    $_.BaseType.FullName -match "System.Enum"
    
    #Below code will give you the detail.
    $xl.GetType().Assembly.GetExportedTypes() | Where-Object -FilterScript {$_.BaseType.Name } | ForEach-Object {$_.BaseType | Format-List * ;break}
    
    • #146337

      Participant
      Topics: 73
      Replies: 119
      Points: 506
      Rank: Major Contributor

      Much thanks, Mr KVprasoon ... for a very enlightening reply. Yes, I was not aware of .Name or .Fullname. In my early efforts to figure this out, I piped to Get-Member (GM) the result of

      $x1.GetType().Assembly.GetExportedTypes()

      and just stopped there. Please let me share my "trial and error" efforts before my post. In order to "figure out" my dilemma I first did the ff:

      # -- this yields Selected.System.RuntimeType
      $x1.GetType().Assembly.GetExportedTypes() | GM

      to see what's "available". In hindsight maybe the correct "line of thinking" should have been:

      # -- this yields Microsoft.PowerShell.Commands.MemberDefinition
      $x1.GetType().Assembly.GetExportedTypes() | GM | GM

      In both cases there is a Name property, but which one is the logically correct? Is my "logic" (or lack of it) consistent within PowerShell's way of operation?
      Thanking you in advance.

The topic ‘Getting all the EXCEL enums’ is closed to new replies.