Lee Evans Posted November 24, 2008 Share Posted November 24, 2008 (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 November 24, 2008 by Lee Evans Link to comment Share on other sites More sharing options...
Lee Evans Posted November 24, 2008 Author Share Posted November 24, 2008 (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 November 24, 2008 by Lee Evans Link to comment Share on other sites More sharing options...
Malkey Posted November 24, 2008 Share Posted November 24, 2008 (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 November 24, 2008 by Malkey Link to comment Share on other sites More sharing options...
Lee Evans Posted November 24, 2008 Author Share Posted November 24, 2008 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. Link to comment Share on other sites More sharing options...
ProgAndy Posted November 24, 2008 Share Posted November 24, 2008 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 Link to comment Share on other sites More sharing options...
Lee Evans Posted November 24, 2008 Author Share Posted November 24, 2008 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 Link to comment Share on other sites More sharing options...
Lee Evans Posted November 25, 2008 Author Share Posted November 25, 2008 In case anyone was interested both solutions worked!! Thanks for all the help Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now