Making Borders in Excel

[CE101]

[AutoIT-Forum] Making Borders in Excel

I am creating a script that generates an Excel report that requires borders around all the cells. Not knowing how this is done I turned on the macro recorder to see what's happening behind the scenes when you create borders.

Here's the VBA code that gets generated....

Cells.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

Now the question - how is this done in AutoIT. As you may know from my previous posting on Sorting with Excel, I do not have a handle on this stuff.

I checked the AutoIT forum and found the following example give at

http://www.autoitscript.com/forum/index.php?showtopic=97157&st=0&p=698557&hl=excel%20%20%20borders&fromsearch=1&#entry698557

Local $A = 204

; Method #1
With $oExcel.ActiveSheet
    .range("A" & $A & ":i51" ).Select
    .range("A" & $A & ":i51" ).Borders($xlEdgeBottom).LineStyle = $xlContinuous
    .range("A" & $A & ":i51" ).Borders($xlEdgeBottom).Weight = $xlThick
    .range("A" & $A & ":i51" ).Borders($xlEdgeBottom).ColorIndex = $xlAutomatic
EndWith

; Method #2
With $oExcel.ActiveSheet.range("A" & $A & ":i51" )
    .Select
    .Borders($xlEdgeBottom).LineStyle = $xlContinuous
    .Borders($xlEdgeBottom).Weight = $xlThick
    .Borders($xlEdgeBottom).ColorIndex = $xlAutomatic
EndWith

Neither of them work for me.

The entire worksheet gets selected, however nothing happens after that. No borders.

I've tried numerous variations of the "with" statement, to no avail . I usually get syntax errors.

expandcollapse popup

;~ ; The requested action with this object has failed.
With $oExcel.ActiveSheet
    .Cells.Select
    .Borders($xlEdgeBottom).LineStyle = $xlContinuous
    .Borders($xlEdgeBottom).Weight = $xlThick
    .Borders($xlEdgeBottom).ColorIndex = $xlAutomatic
EndWith

;~ ; The requested action with this object has failed.
With $oExcel.ActiveSheet
    .Cells.Select
    .Borders($xlEdgeBottom).LineStyle = $xlContinuous
EndWith

;~ ; The requested action with this object has failed.
With $oExcel.ActiveSheet
    .Cells.Select
    .Selection.Borders($xlDiagonalDown).LineStyle = $xlNone
    .Selection.Borders($xlDiagonalUp).LineStyle = $xlNone
    With .Selection.Borders($xlEdgeLeft)
        .LineStyle = $xlContinuous
        .Weight = $xlThin
        .ColorIndex = $xlAutomatic
    EndWith 
EndWith

;~ ; The requested action with this object has failed.
With $oExcel.ActiveSheet
    .Cells.Select
    .Selection.Borders($xlDiagonalDown).LineStyle = $xlNone
    .Selection.Borders($xlDiagonalUp).LineStyle = $xlNone
EndWith

; The requested action with this object has failed. --- on the second "with" statement
With $oExcel.ActiveSheet
    .Cells.Select
;   .Borders($xlDiagonalDown).LineStyle = $xlNone
;   .Borders($xlDiagonalUp).LineStyle = $xlNone
EndWith
With $oExcel.ActiveSheet.Borders($xlEdgeLeft)
    .Cells.Select
    .LineStyle = $xlContinuous
    .Weight = $xlThin
    .ColorIndex = $xlAutomatic
EndWith

;~ The requested action with this object has failed. --- on the second "with" statement
With $oExcel.ActiveSheet
    .Cells.Select
;   .Borders($xlDiagonalDown).LineStyle = $xlNone
;   .Borders($xlDiagonalUp).LineStyle = $xlNone
EndWith
With $oExcel.ActiveSheet
    .Cells.Select
    .Borders($xlEdgeLeft).LineStyle = $xlContinuous
    .Borders($xlEdgeLeft).Weight = $xlThin
    .Borders($xlEdgeLeft).ColorIndex = $xlAutomatic
EndWith

I also tried the following without the "with" statement. No syntax errors, however no borders either.

$oExcel.ActiveSheet.Cells.Select
sleep(200)
$oExcel.ActiveSheet.Cells.Select.Borders($xlEdgeLeft).LineStyle = $xlContinuous
$oExcel.ActiveSheet.Cells.Select.Borders($xlEdgeLeft).Weight = $xlThin
$oExcel.ActiveSheet.Cells.Select.Borders($xlEdgeLeft).ColorIndex = $xlAutomatic
sleep(200)
$oExcel.ActiveSheet.Cells.Select.Borders($xlEdgeRight).LineStyle = $xlContinuous
$oExcel.ActiveSheet.Cells.Select.Borders($xlEdgeRight).Weight = $xlThin
$oExcel.ActiveSheet.Cells.Select.Borders($xlEdgeRight).ColorIndex = $xlAutomatic
sleep(200)
$oExcel.ActiveSheet.Cells.Select.Borders($xlEdgeTop).LineStyle = $xlContinuous
$oExcel.ActiveSheet.Cells.Select.Borders($xlEdgeTop).Weight = $xlThin
$oExcel.ActiveSheet.Cells.Select.Borders($xlEdgeTop).ColorIndex = $xlAutomatic
sleep(200)
$oExcel.ActiveSheet.Cells.Select.Borders($xlEdgeBottom).LineStyle = $xlContinuous
$oExcel.ActiveSheet.Cells.Select.Borders($xlEdgeBottom).Weight = $xlThin
$oExcel.ActiveSheet.Cells.Select.Borders($xlEdgeBottom).ColorIndex = $xlAutomatic
sleep(200)
$oExcel.ActiveSheet.Cells.Select.Borders($xlInsideHorizontal).LineStyle = $xlContinuous
$oExcel.ActiveSheet.Cells.Select.Borders($xlInsideHorizontal).Weight = $xlThin
$oExcel.ActiveSheet.Cells.Select.Borders($xlInsideHorizontal).ColorIndex = $xlAutomatic
sleep(200)
$oExcel.ActiveSheet.Cells.Select.Borders($xlInsideVertical).LineStyle = $xlContinuous
$oExcel.ActiveSheet.Cells.Select.Borders($xlInsideVertical).Weight = $xlThin
$oExcel.ActiveSheet.Cells.Select.Borders($xlInsideVertical).ColorIndex = $xlAutomatic

Any suggestions would be greatly appreciated. Sample code would be even better.

[enaiman]

This works:

#include <Excel.au3>

$oExcel = _ExcelBookNew()
With $oExcel.ActiveSheet.range("A51:i51" )
    .Select
    .Borders($xlEdgeBottom).LineStyle = $xlContinuous
    .Borders($xlEdgeBottom).Weight = $xlThick
    .Borders($xlEdgeBottom).ColorIndex = $xlAutomatic
    .Borders($xlEdgeTop).LineStyle = $xlContinuous
    .Borders($xlEdgeTop).Weight = $xlThick
    .Borders($xlEdgeTop).ColorIndex = $xlAutomatic
    .Borders($xlEdgeLeft).LineStyle = $xlContinuous
    .Borders($xlEdgeLeft).Weight = $xlThick
    .Borders($xlEdgeLeft).ColorIndex = $xlAutomatic
    .Borders($xlEdgeRight).LineStyle = $xlContinuous
    .Borders($xlEdgeRight).Weight = $xlThick
    .Borders($xlEdgeRight).ColorIndex = $xlAutomatic
EndWith
Sleep(20000)
_ExcelBookClose($oExcel)

It will draw a thick border around selected area.

[CE101]

Hi enaiman -- thank you for the code.

While waiting for some suggestions I did further research and found some VBA examples at http://technet.microsoft.com/en-us/library/ee692886.aspx . And based on them I wrote the following code which works fine (borders around all cells)...

$oExcel.ActiveSheet.UsedRange.Borders.LineStyle = $xlContinuous
$oExcel.ActiveSheet.UsedRange.Borders.Weight = $xlThin
$oExcel.ActiveSheet.UsedRange.Borders.ColorIndex = $xlAutomatic