Sign in to follow this  
Followers 0
Lee Evans

Excel condiotional formating using COM objects

7 posts in this topic

#1 ·  Posted (edited)

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 by Lee Evans

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by Lee Evans

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by Malkey

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

In case anyone was interested both solutions worked!!

Thanks for all the help

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  
Followers 0