Windows Vista Forums

Borders in Excel
  1. #1


    Jacob Sampson Guest

    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

  2. #2


    Kiron Guest

    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

Borders in Excel problems?

Similar Threads
Thread Thread Starter Forum Replies Last Post
borders in publisher onward Microsoft Office 0 31 Dec 2009
VBscript applying borders in Excel Codeblack VB Script 0 11 Sep 2008
How to view Excel document without Microsoft Office Excel installe jl Vista General 8 20 Apr 2008
Vista ASP.NET 2.0 Cannot open Excel File using Microsoft.Excel 12.0 COM object ckellywilson Microsoft Office 0 26 Mar 2008
Printing borders Amber Vista print fax & scan 0 11 Mar 2008