Using the BorderAround Method in Excel

I was messing around with the borders function in Excel and I noticed that there is also a BorderAround function.

1. Here is the Borders Function:

$xl = new-object -comobject Excel.Application
# make Excel visible
$xl.visible = $true
$xl.DisplayAlerts = $False
# open a workbook
$wb = $xl.workbooks.add()
# Get sheet1
$ws1 = $wb.worksheets | where {$_.name -eq "sheet1"}

$xlAutomatic=-4105
$xlBottom = -4107
$xlCenter = -4108
$xlContext = -5002
$xlContinuous=1
$xlDiagonalDown=5
$xlDiagonalUp=6
$xlEdgeBottom=9
$xlEdgeLeft=7
$xlEdgeRight=10
$xlEdgeTop=8
$xlInsideHorizontal=12
$xlInsideVertical=11
$xlNone=-4142
$xlThin=2
$xlMedium = -4138
$xlThick = 4

$selection = $ws1.usedRange
[void]$selection.select()
$selection.Borders.Item($xlEdgeLeft).LineStyle = $xlContinuous
$selection.Borders.Item($xlEdgeLeft).ColorIndex = $xlAutomatic
$selection.Borders.Item($xlEdgeLeft).Color = 1
$selection.Borders.Item($xlEdgeLeft).Weight = $xlMedium
$selection.Borders.Item($xlEdgeTop).LineStyle = $xlContinuous
$selection.Borders.Item($xlEdgeBottom).LineStyle = $xlContinuous
$selection.Borders.Item($xlEdgeRight).LineStyle = $xlContinuous
$selection.Borders.Item($xlInsideVertical).LineStyle = $xlContinuous
$selection.Borders.Item($xlInsideHorizontal).LineStyle = $xlContinuous

This puts borders around all the cells in the used range.

I tried using the BorderAround Function like this:

$selection = $ws1.usedRange
[void]$selection.select()

$selection.BorderAround

And I got this:

MemberType          : Method
OverloadDefinitions : {Variant BorderAround (Variant, XlBorderWeight, XlColorIndex, Variant)}
TypeNameOfValue     : System.Management.Automation.PSMethod
Value               : Variant BorderAround (Variant, XlBorderWeight, XlColorIndex, Variant)
Name                : BorderAround
IsInstance          : True

So I plugged in some numbers here and there and wound up with this:

$selection.BorderAround(1,4,3); # 1 = Continuous 4 = thick and 3 = Red

Which puts a thick Red border around the whole selection.

So, my question was, What does this mean:
Specifies the line style for the border.

Name Value Description
xlContinuous 1

Continuous line.

xlDash -4115

Dashed line.

xlDashDot 4

Alternating dashes and dots.

xlDashDotDot 5

Dash followed by two dots.

xlDot -4118

Dotted line.

xlDouble -4119

Double line.

xlLineStyleNone -4142

No line.

xlSlantDashDot 13

Slanted dashes.

OverloadDefinitions : {Variant BorderAround (Variant, XlBorderWeight, XlColorIndex,Variant)}

And I was directed to MSDN (http://msdn.microsoft.com/en-us/library/bb209714(office.12).aspx)

Name Required/Optional Data Type Description
LineStyle Optional Variant One of the constants of XlLineStyle
specifying the line style for the border.
Weight Optional XlBorderWeight The border weight.
ColorIndex Optional XlColorIndex The border color, as an index into the current color
palette or as a XlColorIndex constant.
Color Optional Variant The border color, as an RGB value.

BTW, as far as I know Powershell does not do RGB, so the last variant is not used.

Advertisements

One thought on “Using the BorderAround Method in Excel

  1. (1) You can see the defintion of the BorderAround-method in

    http://msdn.microsoft.com/en-us/library/bb209714%28v=office.12%29.aspx

    and you can use the enumerations like XlBorderWeight directly in PowerShell after you have created the Excel.Application COM-Object with

    $xlThick = [Microsoft.Office.Interop.Excel.XlBorderWeight]::xlThick;

    (2) And instead of using

    $wb.worksheets | where {$_.name -eq “sheet1?}

    it is shorter, if you use the name directly like

    $wb.worksheets.Item(“sheet1”)

    or for international usage (because in Germany it id “Tabelle1” 😉

    $wb.worksheets.Item(1)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s