Jump to content
Sign in to follow this  
CE101

Making Borders in Excel

Recommended Posts

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.

;~ ; 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.

Share this post


Link to post
Share on other sites
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.


SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script

wannabe "Unbeatable" Tic-Tac-Toe

Paper-Scissor-Rock ... try to beat it anyway :)

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.