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


Yet another fine example from Kiron on the Powershell discussion board.
http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/threads
You can use the BorderAround() Method to set a border on a range:

# some Excel Constants used in the sample 
# for more Constants you can look here
# http://cid-c2db05eefa6c21a1.skydrive.live.com/browse.aspx/Public 
# 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
 
Advertisements

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