Office automation: Accessing constants such as xlCenter

When automating Office via COM using PowerShell, you can work out what methods to call using the Microsoft.Office.Interop Document Object Model or you can run Office, record a macro doing what you want, then edit it to see the VBA that would do what you want. From there it’s easy to find out what the methods and properties that you need are.

Something less obvious is when a method uses a constant, such as xlCenter, how can you access that constant? They’re stored in an enumeration called Constants. If anyone knows how to access these enumerations in PowerShell, please let me know. Otherwise, you have two methods.

Spy on the values in the Macro Editor

  1. Record a macro in Office that does what you want.
  2. Open it in the macro editor
  3. Start debugging it with Step into…
  4. Hover over the constant to see its value

Using the Macro Editor to find constant values

Look in the object model

Only works for constants like xlCenter.

  1. Find out what integer the constant corresponds to by finding it in this list (could be negative)
  2. Create an integer object set to this value
  3. Reference it in your COM call.

Example:

$excel = new-object -comobject Excel.Application
$workbooks = $excel.Workbooks.Add()
$worksheets = $workbooks.worksheets
$worksheet = $worksheets.Item(1)
$worksheet.Name = "Name of Worksheet"
$excel.Visible = $True
$worksheet.Cells.Item(3,3) = "Title of Excel Document"
$centre = [int] -4108
$worksheet.Cells.HorizontalAlignment = $centre
Advertisements