Excel condiotional formating using COM objects

[Lee Evans]

Can someone help me with Excel condiotional formating using COM objects? I am trying to recreate an example from Excel 2003s help in AutoIT.

However I keep getting an error report in Scite output window.

C:\Program Files\AutoIt3\Include\Examples\_ExcelConditionalFormat.au3 (29) : ==> The requested action with this object has failed.:

.Range($sRange).FormatConditions .Add('xlCellValue', 'xlGreater', '"=$b$1"')

.Range($sRange).FormatConditions .Add('xlCellValue', 'xlGreater', '"=$b$1"')^ ERROR

->AutoIT3.exe ended.rc:1

I have tried to follow the examples in the Excel.au3 UDF and match them up to the relevant examples in the excel help but I can't work this one out.

I know that you cannot nest With statements but the problem is reported before where the with statements were. How can I do this in AutoIT or cannot it not be done.

Here is the VBA example

With Worksheets(1).Range("e1:e10").FormatConditions _
        .Add(xlCellValue, xlGreater, "=$a$1")
    With .Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 6
    End With
    With .Font
        .Bold = True
        .ColorIndex = 3
    End With
End With

Here is my AutoIT attempt that gives the reported error.

#include <Excel.au3>

Local $oExcel = _ExcelBookNew();Create new book, make it visible
; We can fill-up some cells using a simple loop and random Numbers
For $y = 1 To 3
    For $x = 1 To 3
        _ExcelWriteCell($oExcel, Random(1000, 10000), $x, $y);Some random numbers to file
    Next
Next


$sRange = "b2:b3"
_ExcelConditionalFormat($oExcel,$sRange)

Func _ExcelConditionalFormat($oExcel, $sRange)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
;$oExcel.ActiveSheet.Range($sRangeOrRow).Select
        With $oExcel.ActiveSheet
    ;$oExcel.Selection.FormatConditions.Add('xlCellValue', 'xlGreater', '"=$b$1"')
            .Range($sRange).FormatConditions _
            .Add('xlCellValue', 'xlGreater', '"=$b$1"')
            
            .Borders
                .Linestyle = $xlContinuous
                .Weight = $xlThin
                .ColorIndex = 6
            
            
            .Font
                .Bold = True
                .ColorIndex = 3
            
        EndWith
        Return 1
    
EndFunc ;==>_ExcelConditionalFormat

I think my problem is that looking at the examples in the excelCOM and excel libraries the xl values are replaced by numerical constants but how do I find these out for variables that are not defined in these libraries. E.g. in my example xlcellvalue and xlgreater

Edited November 24, 2008 by Lee Evans

[Lee Evans]

I have progressed a little since first post and have found the enumerated constants I needed thanks to Get application Enums

But I am still getting an error when setting the font of the condition. The border works correctly but get the following error when setting the font

C:\Program Files\AutoIt3\Include\Examples\_ExcelConditionalFormat.au3 (33) : ==> The requested action with this object has failed.:

.Font

.Font^ ERROR

->AutoIT3.exe ended.rc:1

Here is my modify code with all the constants I need in the include

#include <ExcelCOM_UDF_allconst.au3>

Local $oExcel = _ExcelBookNew ();Create new book, make it visible
; We can fill-up some cells using a simple loop and random Numbers
For $y = 1 To 3
    For $x = 1 To 3
        _ExcelWriteCell ($oExcel, Random(1000, 10000), $x, $y);Some random numbers to file
    Next
Next

ToolTip("Formatting Column(s) Soon...")
Sleep(3500);Pause to let user view action

; We can format using a simple loop
; Each Column will have a differnt type of Format
$sRange = "b2:b3"
_ExcelConditionalFormat($oExcel, $sRange)

Func _ExcelConditionalFormat($oExcel, $sRange)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
        
    With $oExcel.ActiveSheet.Range ($sRange).FormatConditions.Add ($xlCellValue, $xlGreater, '=$b$1').Borders
        .Linestyle = $xlContinuous
        .Weight = $xlThin
        .ColorIndex = 3
        .Font
        .Bold = True
        .ColorIndex = 3
    EndWith
    
    Return 1
    
EndFunc ;==>_ExcelConditionalFormat

Edited November 24, 2008 by Lee Evans

[Malkey]

Try this.

;#include <ExcelCOM_UDF_allconst.au3>
#include <Excel.au3>

Global Const $xlCellValue = 1
Global Const $xlGreater = 5
Local $oExcel = _ExcelBookNew();Create new book, make it visible
; We can fill-up some cells using a simple loop and random Numbers
For $y = 1 To 3
    For $x = 1 To 3
        _ExcelWriteCell($oExcel, Random(1000, 10000), $x, $y);Some random numbers to file
    Next
Next

ToolTip("Formatting Column(s) Soon...")
Sleep(3500);Pause to let user view action

; We can format using a simple loop
; Each Column will have a differnt type of Format
$sRange = "b2:b3"
_ExcelConditionalFormat($oExcel, $sRange)

Func _ExcelConditionalFormat($oExcel, $sRange)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)

    With $oExcel.ActiveSheet.Range($sRange).FormatConditions.Add($xlCellValue, $xlGreater, '=$b$1' ).Borders
        .Linestyle = $xlContinuous
        .Weight = $xlThin
        .ColorIndex = 3
    EndWith
    With $oExcel.ActiveSheet.Range($sRange).Font
        .Name = "Arial"
        .Bold = True
        .ColorIndex = 3
    EndWith
    Return 1
EndFunc   ;==>_ExcelConditionalFormat

Edit: Running:(3.2.13.11)AutoIt Beta

Edited November 24, 2008 by Malkey

[Lee Evans]

I have tried the above before posting here in the forum and what that does is to set the font colour etc for the cells but it is not in the conditional formating.

The VBA example sets the font colour as well as the border in the conditional formating and this is what I want to do.

[ProgAndy]

You have to assign ...Condition.Add( to a new variable and use this with the With blocks

#include<Excel\Excel.au3>

Global Const $xlCellValue = 1
Global Const $xlGreater = 5
Local $oExcel = _ExcelBookNew();Create new book, make it visible
; We can fill-up some cells using a simple loop and random Numbers
For $y = 1 To 3
    For $x = 1 To 3
        _ExcelWriteCell($oExcel, Random(1000, 10000), $x, $y);Some random numbers to file
    Next
Next

ToolTip("Formatting Column(s) Soon...")
Sleep(3500);Pause to let user view action

; We can format using a simple loop
; Each Column will have a differnt type of Format
$sRange = "a2:c3"
_ExcelConditionalFormat($oExcel, $sRange)

Func _ExcelConditionalFormat(ByRef $oExcel, $sRange)

Local $newFormat = $oExcel.ActiveSheet.Range($sRange).FormatConditions.Add($xlCellValue, $xlGreater, "=$a$1")
    With $newFormat.Borders
        .LineStyle = $xlContinuous
        .Weight = $xlThin
        .ColorIndex = 6
    EndWith
    With $newFormat.Font
        .Bold = True
        .ColorIndex = 3
    EndWith

EndFunc

[Lee Evans]

Thanks for the above I haven't tested it yet but in between posts I got this to work for me

#include <ExcelCOM_UDF_allconst.au3>

Local $oExcel = _ExcelBookNew();Create new book, make it visible
; We can fill-up some cells using a simple loop and random Numbers
For $y = 1 To 3
    For $x = 1 To 3
        _ExcelWriteCell($oExcel, Random(1000, 10000), $x, $y);Some random numbers to file
    Next
Next

ToolTip("Formatting Column(s) Soon...")
Sleep(3500);Pause to let user view action

; We can format using a simple loop
; Each Column will have a differnt type of Format
$sRange = "b2:b3"
_ExcelConditionalFormat($oExcel, $sRange)

Func _ExcelConditionalFormat($oExcel, $sRange)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)

    With $oExcel.ActiveSheet.Range($sRange).FormatConditions.Add($xlCellValue, $xlGreater, '=$b$1' ).Borders
        .Linestyle = $xlContinuous
        .Weight = $xlThin
        .ColorIndex = 3
    EndWith
    With $oExcel.ActiveSheet.Range($sRange).FormatConditions(1).Font
         .Bold = True
         .ColorIndex = 3
    EndWith
    
    Return 1
EndFunc  ;==>_ExcelConditionalFormat

[Lee Evans]

In case anyone was interested both solutions worked!!

Thanks for all the help