Excel Rahmenlinien einfügen

  • Hallo zusammen,

    Wie kann ich mit AutoIT in ein ExcelSheet Rahmenlinien Einfügen? MEin Problem ist, dass die Gitterlinien im Excel verschwinden, sobald ich Zellen farbig hinterlege. Darum würde ich nachträglich gerne Rahmenlinien hinzufügen. Für $oExcel.ActiveWorkbook.Sheets(1).Range(A1:M100) zum Beispiel.

    • Offizieller Beitrag
    Spoiler anzeigen
    [autoit]

    ;===============================================================================
    ;
    ; Description: Create Borders around a range of cells
    ; Syntax: _ExcelCreateBorders($oExcel, $sBorderStyle, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iEdgeLeft = 1,
    ; $iEdgeTop = 1, $iEdgeBottom = 1, $iEdgeRight = 1, $iEdgeInsideV = 0, $iEdgeInsideH = 0)
    ; Parameter(s): $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
    ; $sBorderStyle - The type of border to use, $xlThick, $xlThin, $xlDouble
    ; $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1
    ; $iColStart - The starting column for the number format(left) (default=1)
    ; $iRowEnd - The ending row for the number format (bottom) (default=1)
    ; $iColEnd - The ending column for the number format (right) (default=1)
    ; $iEdgeLeft - Specify if the left edge of the selected cells should have a border (default=1) Yes
    ; $iEdgeTop - Specify if the Top edge of the selected cells should have a border (default=1) Yes
    ; $iEdgeBottom - Specify if the Bottom edge of the selected cells should have a border (default=1) Yes
    ; $iEdgeRight - Specify if the Right edge of the selected cells should have a border (default=1) Yes
    ; $iEdgeInsideV - Specify if the Inside Verticle edges of the selected cells should have a border (default=0) No
    ; $iEdgeInsideH - Specify if the Inside Horizontal edges of the selected cells should have a border (default=0) No
    ; Requirement(s): None
    ; Return Value(s): On Success - Returns 1
    ; On Failure - Returns 0 and sets @error on errors:
    ; @error=1 - Specified object does not exist
    ; @error=2 - Starting row or column invalid
    ; @extended=0 - Starting row invalid
    ; @extended=1 - Starting column invalid
    ; @error=3 - Ending row or column invalid
    ; @extended=0 - Ending row invalid
    ; @extended=1 - Ending column invalid
    ; Author(s): MikeOsdx <Using Generic Excel functions from locodarwin>
    ; Note(s): None
    ;
    ;===============================================================================
    Func _ExcelCreateBorders($oExcel, $sBorderStyle, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, _
    $iEdgeLeft = 1, $iEdgeTop = 1, $iEdgeBottom = 1, $iEdgeRight = 1, $iEdgeInsideV = 0, $iEdgeInsideH = 0)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then
    If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)
    If $iColStart < 1 Then Return SetError(2, 1, 0)
    If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)
    If $iColEnd < $iColStart Then Return SetError(3, 1, 0)
    if $iEdgeLeft = 1 Then
    With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlEdgeLeft)
    .LineStyle = $xlContinuous
    .ColorIndex = $xlAutomatic
    .TintAndShade = 0
    .Weight = $sBorderStyle
    EndWith
    EndIf
    if $iEdgeTop = 1 Then
    With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlEdgeTop)
    .LineStyle = $xlContinuous
    .ColorIndex = $xlAutomatic
    .TintAndShade = 0
    .Weight = $sBorderStyle
    EndWith
    EndIf
    if $iEdgeBottom = 1 Then
    With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlEdgeBottom)
    .LineStyle = $xlContinuous
    .ColorIndex = $xlAutomatic
    .TintAndShade = 0
    .Weight = $sBorderStyle
    EndWith
    EndIf
    if $iEdgeRight = 1 Then
    With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlEdgeRight)
    .LineStyle = $xlContinuous
    .ColorIndex = $xlAutomatic
    .TintAndShade = 0
    .Weight = $sBorderStyle
    EndWith
    EndIf
    if $iEdgeInsideV = 1 Then
    With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlInsideVertical)
    .LineStyle = $xlContinuous
    .ColorIndex = $xlAutomatic
    .TintAndShade = 0
    .Weight = $sBorderStyle
    EndWith
    EndIf
    if $iEdgeInsideH = 1 Then
    With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlInsideHorizontal)
    .LineStyle = $xlContinuous
    .ColorIndex = $xlAutomatic
    .TintAndShade = 0
    .Weight = $sBorderStyle
    EndWith
    EndIf
    Else
    if $iEdgeLeft = 1 Then
    With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlEdgeLeft)
    .LineStyle = $xlContinuous
    .ColorIndex = $xlAutomatic
    .TintAndShade = 0
    .Weight = $sBorderStyle
    EndWith
    EndIf
    if $iEdgeTop = 1 Then
    with $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlEdgeTop)
    .LineStyle = $xlContinuous
    .ColorIndex = $xlAutomatic
    .TintAndShade = 0
    .Weight = $sBorderStyle
    EndWith
    EndIf
    if $iEdgeBottom = 1 Then
    With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlEdgeBottom)
    .LineStyle = $xlContinuous
    .ColorIndex = $xlAutomatic
    .TintAndShade = 0
    .Weight = $sBorderStyle
    EndWith
    EndIf
    if $iEdgeRight = 1 Then
    With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlEdgeRight)
    .LineStyle = $xlContinuous
    .ColorIndex = $xlAutomatic
    .TintAndShade = 0
    .Weight = $sBorderStyle
    EndWith
    EndIf
    if $iEdgeInsideV = 1 Then
    With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlInsideVertical)
    .LineStyle = $xlContinuous
    .ColorIndex = $xlAutomatic
    .TintAndShade = 0
    .Weight = $sBorderStyle
    EndWith
    EndIf
    if $iEdgeInsideH = 1 Then
    With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlInsideHorizontal)
    .LineStyle = $xlContinuous
    .ColorIndex = $xlAutomatic
    .TintAndShade = 0
    .Weight = $sBorderStyle
    EndWith
    EndIf
    EndIf
    Return 1
    EndFunc

    [/autoit]
  • Super! Vielen Dank! eine tolle funktion :) lässt sich wunderbar an eigene Wünsche und Vorstellungen anpassen.