Author Posts

June 14, 2018 at 7:44 am

I have a portion of a PS script that is as follows:

$range = "B2:F100"
$sheet.Range($range).Clear()
$sheet.Range($range).NumberFormat = "General"
$sheet.Range($range).RowHeight = 16
$sheet.Range($range).ColumnWidth = 25
$sheet.Range($range).Font.Name = "Calibri"
$sheet.Range($range).Font.Size = 10
$sheet.Range($range).Font.Color = ???
$sheet.Range($range).HorizontalAlignment = "xlLeft"
$sheet.Range($range).VerticalAlignment = "xlBottom"

I am stucked with the last 3 lines. In reading Microsoft documentation for EXCEL objects it seems there are many methods and properties that do not translate readily to the PS environment. So far, in the above list, I managed — by trial and error, other Forum examples, etc. — to get the first 5 lines (beginning with ....NumberFormat = General) to work properly. But I cannot find the appropriate documentation for the last 3 lines. There are also other properties that seem impossible to change or set in EXCEL, such as Borders, Background Color (Fill).

Any tips, advice or references will be highly appreciated.
Thanks.

June 14, 2018 at 10:19 pm

The enums and helper methods which are usually used for Excel programming are not immediately available in PowerShell but you can recreate what you need.

For Color you can use a helper method like:

function RGB ([int] $red, [int] $green, [int] $blue)
{
    return $red + ($green * 255) + ($blue * 65536)
}
$sheet.Range($range).Font.Color = RGB 255 0 0 #Make text red

For alignments you can assign the integer value of the enum or regenerate the enum in PowerShell and reference it. Here are the values you may care about.

$sheet.Range($range).HorizontalAlignment = 2 #General = 1, Left = 2 , Center = 3, Right = 4, Fill = 5 , Justify = 6, Center Across Section = 7, Distributed = 8
$sheet.Range($range).VerticalAlignment = -4107 #Bottom -4107, Top= -4160, Center=-4108, Justify = -4130, Distributed = -4117

June 15, 2018 at 2:56 am

Hello Mr Crompton,
Thank you very much for your inputs — they are extremely useful for what I am striving to do. While I was looking to various blog sites, I cam across one post which was rather interesting. It had to do with accessing those deeply-buried Enum values and the like. One such example (from the 4 or 5 that the author listed) is (I was interested in changing cell borders) —

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

But when I ran this, I got the following error message:
Unable to find type [Microsoft.Office.Interop.Excel.XlBordersIndex].
At line:2 char:19
+ [Enum]::getvalues([Microsoft.Office.Interop.Excel.XlBordersIndex]) |
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (Microsoft.Offic....XlBordersIndex:TypeName) [], RuntimeExc
eption
+ FullyQualifiedErrorId : TypeNotFound

Clearly, I need to install (?) some missing pieces from Microsoft. It's not critical for me to be able to change borders/etc.. It's just interesting to know that this exists. Would be grateful for any additional tips or pointers. Many thanks!

June 15, 2018 at 4:36 am

Hi Ramon,

What you are trying to do there is access the enum values which are stored in those interop assemblies. You can load those assemblies and then access these but you have to load the assembly into your powershell process first. You can do this by running the following:

[system.reflection.assembly]::LoadWithPartialName("Microsoft.Office.Interop.Excel")

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

will give you access to the .NET enum wrapper for this Excel enum. These interop assemblies contain .Net copies of many of the interfaces and enums used by excel for use when programming in .Net, naturally you can access them this way in PowerShell and depending on how many you need that may be preferable to you than creating your own enum copies in PowerShell.

June 15, 2018 at 5:09 am

Much thanks Mr Crompton. Your answers are extremely helpful and educational for me.
My sincerest gratitude for your assistance.