23

#PSTip Working with Excel constants

When automating Microsoft Excel, almost every time you’ll need to include some constant values to define the behaviour of the code. Excel (and other Office applications) makes extensive use of constant values via enumeration objects. For example, to right align cell content you need to know in advance the value of the xlRight constant. Most of the time you’ll lookup the value using an Internet search or use some VBA-Fu techniques and hard code it in your script.

$xlRight = -4152
$sheet.Range('A1').HorizontalAlignment = $xlRight

In this tip I want to suggest a convenient  way  to work with those constants, a way that utilizes tab completion and that also doesn’t require you to know the numeric value of the constant in question. Now it’s very easy to discover all constants that have ‘right’ in their name.

xlEnum

The following code iterates over all Enumeration objects of Excel, converge all values into one PowerShell custom object. Now whenever you need to specify one of the constants, it’s very easy to discover the value.

# create Excel object
$xl = New-Object -ComObject Excel.Application

# create new PowerShell object
$xlEnum = New-Object -TypeName PSObject

# get all Excel exported types of type Enum
$xl.GetType().Assembly.GetExportedTypes() | Where-Object {$_.IsEnum} | ForEach-Object {

    # create properties from enum values
    $enum = $_
    $enum.GetEnumNames() | ForEach-Object {
        $xlEnum | Add-Member -MemberType NoteProperty -Name $_ -Value $enum::($_)
    }
}

Now you can use constants without hard coding them first.

$sheet.Range('A1').HorizontalAlignment = $xlEnum.xlRight

If you still need to find the numeric value:

PS> $xlEnum.xlRight.value__
-4152
Filed in: Columns, Tips and Tricks Tags: , , , ,

23 Responses to "#PSTip Working with Excel constants"

  1. David Brand says:

    Hello
    I’m trying to enumerate arguments for Excel properties, to know what arguments (eg xlRight, xlTop, …) that a property has, and its values.

    Apologies for asking the extreme basics, but I’m an old-time cmd.exe user who has just started looking at PS.

    1. Do I copy the above code into a file and save is as a .ps (?) script file; then run that file from the PS command prompt “PS c:UsersDavid>”

    2. Is the information exported to a list, or does the object reside in memory (until reboot), and while in memory I can pose questions such as “PS> $xlEnum.xlRight.value”

    Regards, David

    • ShayLevy says:

      Hi David

      The variable that contain the enum values, $xlEnum, is memory resident. To have it available you need to execute the code in each new session (or script). To save it in a script file, save the code in a ps1 file and from your ps prompt load it into memory by executing the script: “PS c:UsersDavid> c:scriptName.ps1. Now $xlEnum will be available in your session and you’ll be able to get the values of enums, such as: $xlEnum.xlRight.value

      • David Brand says:

        Hello Shay. Thank you for your very quick response. I’ve been having a go, but something is not right, so I’ll have another go in the morning (it’s 10:40pm here). David

      • David Brand says:

        Hello Shay
        Running the script gave me the error shown below at 1 –
        repeated 254 times.
        I then discovered I was running PS version 2, so I updated
        to WMF 4.0 with PS version 4.0, and rebooted as requested.
        This time the script ran without errors, but when I ran
        $xlEnum.xlRight.value, nothing appeared to happen – the system just returned to the prompt ie: PS C:UsersDavidDocuments> – refer 2 below.
        I’m running as Administrator, and the execution policy is
        set to Unrestricted.
        Help please.
        ——————————-
        1. PS C:UsersDavidDocuments> .shay.ps1
        Method invocation failed because [System.RuntimeType] doesn’t
        contain a method named ‘GetEnumNames’.
        At C:UsersDavidDocumentsshay.ps1:12 char:23
        + $enum.GetEnumNames <<< .shay.ps1
        PS C:UsersDavidDocuments> $xlEnum.xlRight.value
        PS C:UsersDavidDocuments>

  2. Roger says:

    I’m using Windows 8.1 and Office 365. When I execute this code, I get a bunch of the following errors:

    Add-Member : Cannot add a member with the name “AccessDenied” because a member with that name already exists. To overwrite the member anyway, add the Force parameter to your command.
    At line:6 char:19
    + $xlEnum | Add-Member -MemberType NoteProperty -Name $_ -Value $enum::($_ …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (@{None=None; Re…ags=AuditFlags}:PSObject) [Add-Member], InvalidOperationException
    + FullyQualifiedErrorId : MemberAlreadyExists,Microsoft.PowerShell.Commands.AddMemberCommand

    Add-Member : Cannot add a member with the name “SystemAudit” because a member with that name already exists. To overwrite the member anyway, add the Force parameter to your command.
    At line:6 char:19
    + $xlEnum | Add-Member -MemberType NoteProperty -Name $_ -Value $enum::($_ …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (@{None=None; Re…ags=AuditFlags}:PSObject) [Add-Member], InvalidOperationException
    + FullyQualifiedErrorId : MemberAlreadyExists,Microsoft.PowerShell.Commands.AddMemberCommand

    I don’t see anything to due with Excel. Could it be an Office365 thing?

  3. Shay Levy says:

    Do you get the correct enum objects when you execute:

    $xl.GetType().Assembly.GetExportedTypes()

    If so, save them in a variable and investigate each one, can you get the values of each object?

  4. Jeff Wilson says:

    Hello Shay.
    I have a ? for you. I am working with pivot tables and need to utilize the $XLafter enum…
    I have the code from VMMacro as….
    { ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Submit Date”). _
    ClearAllFilters
    ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Years”).ClearAllFilters
    ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Submit Date”).PivotFilters. _
    Add Type:=xlAfter, Value1:=”1/1/2012″
    }

    And I need to convert this to Powershell, Here is what I have, but it is not working….
    {
    $PivotFields = $Worksheet2.PivotTables(“PivotTable1”).PivotFields(“Submit Date”).clearallfilters
    $PivotFields = $Worksheet2.PivotTables(“PivotTable1”).PivotFields(“Years”).clearallfilters
    $PivotFields = $Worksheet2.PivotTables(“PivotTable1”).PivotFields(“Submit Date”).PivotFilters.Add( [Type]::$xlAfter, {$Value1=”1/1/2012″})
    }

  5. Shay Levy says:

    Hello Jeff

    Maybe this article can help, check the ‘Adding New Filter Types Programmatically’ section.

    http://msdn.microsoft.com/en-us/library/ff536099(v=office.12).aspx

    If you need to pass type.missing to arguments you don’t want to include, pass $missing:

    $missing = [type]::missing

    • Jeff Wilson says:

      Shay, thank you so much for your response, but I seem to need to get better at converting VB Macro Data. So things that look simple to convert from Macro to Powershell, are not so easy. LIke my above question. If I get one of these, i can do others, so i just need a start…
      This line has been stumping me.
      ============================================================
      VBMacroCode
      {
      ActiveSheet.PivotTables(“PivotTable1″).PivotFields(“Submit Date”).PivotFilters. _
      Add Type:=xlAfter, Value1:=”1/1/2012″
      }
      ============================================================
      How do i convert that to POWERSHELL> There has to be something I am missing. Please help.
      Thanks, Jeff W.

  6. Shay Levy says:

    I’d try to use the Add method as any other method (check the signature on msdn or in the article I referenced), pass in the arguments (use missing for empty arguments), it’s a game of trial and error I suppose.

    Looks like you need to pass the first three arguments (http://msdn.microsoft.com/en-us/library/bb178693(v=office.12).aspx), give this a try:

    …PivotFilters.Add($xlAfter, ‘The field to which the filter is attached’, ‘1/1/2012’)

  7. Jeff Wilson says:

    Shay, Thank you so much, you saved me lots of searching. I am getting better at the conversion… That worked Great.. Like a Charm !!!

    So I have another ? Very similar, and should work. I’m getting no Errors, but it does not SORT…
    your thoughts?
    {
    $PivotFields = $Worksheet3.PivotTables(“PivotTable2”).PivotFields(“Request Type”)
    $PivotFields.AutoSort.($xlDescending, “Count of Status”)
    }
    Something is obviously wrong, don’t know what that is… It looks correct..

  8. Jeff Wilson says:

    Here is the actual Macro Code.
    {
    ActiveSheet.PivotTables(“PivotTable2”).PivotFields(“Request Type”).AutoSort _
    xlDescending, “Count of Status”
    }

  9. Jeff Wilson says:

    So I answered my own ? but paying close attention to detail. NOTE To self, don’t forget to remove the period before a perenthisis.
    {
    $PivotFields = $Worksheet3.PivotTables(“PivotTable2”).PivotFields(“Request Type”)
    $PivotFields.AutoSort($xlDescending, “Count of Status”)
    }
    Working Code.

    YEH< Thanks Shay..

Leave a Reply

Submit Comment

© 2016 PowerShell Magazine. All rights reserved. XHTML / CSS Valid.
Proudly designed by Theme Junkie.
%d bloggers like this: