Borders in Excel

[Jacob Sampson]

I am suprised at how hard a time I have had trying to find out how to place a
border around a range of cells in excel. I have read some of the posts on
this site but they dont make much sense to me.

I can open a workbook, create the worksheets and select a range of cells. I
can even merge those cells but how to place a simple black border around the
range has me stumped.

Thanks for the help.

[Kiron]

You can use the BorderAround() Method to set a border on a range:

# some Excel Constants used in the sample

# line styles
$xlLineStyleNone = -4142
$xlContinuous = 1
$xlDash = -4115

# line weight
$xlThin = 2
$xlMedium = -4138
$xlThick = 4

# color index
$xlColorIndexBlue = 5 # <-- depends on default palette

# border index
$xlEdgeLeft = 7
$xlEdgeTop = 8
$xlEdgeBottom = 9
$xlEdgeRight = 10

# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - #

$xl = new-object -c excel.application
$wb = $xl.workbooks.add()
$sh = $wb.worksheets.item('Sheet1')
$xl.visible = $true
$range1 = $sh.range('b2:d4')
$range2 = $sh.range('f2:h4')
$range3 = $sh.range('b6:d8')
$range4 = $sh.range('f6:h8')

# thin blue border on a $range1; [void] suppresses the method's output
[void]$range1.borderAround($xlContinuous,$xlThin,$xlColorIndexBlue)

# set a medium border on $range2 (automatic color index)
[void]$range2.borderAround($xlContinuous,$xlMedium)

# medium blue dash border on $range3
[void]$range3.borderAround($xlDash,$xlMedium,$xlColorIndexBlue)

# set a thick line border on $range4 (automatic color index)
[void]$range4.borderAround($xlContinuous,$xlThick)

# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - #

# to clear a range's borders, set its LineStyle to $xlLineStyleNone

# clear $range2's borders, quick but not the safest way because it'll
# also clear all borders on every cell within the range
$range2.borders.lineStyle = $xlLineStyleNone

# clear $range4's borders, safer, it clears specific borders one at
# a time
$xlEdgeLeft, $xlEdgeTop, $xlEdgeBottom, $xlEdgeRight | % {
$range4.borders.item($_).lineStyle = $xlLineStyleNone
}

# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - #

# close and release resources
$wb.close($false)
$xl.quit()
spps -n excel

--
Kiron