Windows Vista Forums
Vista Forums Home Join Vista Forums Windows 7 Forum Vista Tutorials Tags
Welcome to Windows Vista Forums. Our forum is dedicated to helping you find solutions with any problems, errors or issues you are experiencing with Windows Vista. The Vista forum also covers news and updates and has an extensive Windows Vista tutorial section that covers a wide range of tips and tricks.

Go Back   Vista Forums > Misc Newsgroups > PowerShell

Vista - Borders in Excel

Reply
 
Old 10-11-2008   #1 (permalink)
Jacob Sampson


 
 

Borders in Excel

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.

My System SpecsSystem Spec
Old 10-12-2008   #2 (permalink)
Kiron


 
 

Re: Borders in Excel

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
My System SpecsSystem Spec
Reply

Thread Tools


Similar Threads
Thread Forum
VBscript applying borders in Excel VB Script
How to view Excel document without Microsoft Office Excel installe Vista General
Vista ASP.NET 2.0 Cannot open Excel File using Microsoft.Excel 12.0 COM object Microsoft Office
Printing borders Vista print fax & scan
Program borders Vista General


Vista Forums is an independent web site and has not been authorized,
sponsored, or otherwise approved by Microsoft Corporation.
"Windows Vista", the Start Orb, and related materials are trademarks of Microsoft Corp.
© Designer Media Ltd

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46