Jump to content
kcvinu

Can i change cell color in excel with autoit ?

Recommended Posts

Hi all,

I have a work book which contained some date values. So each time i opened this book, i need to change the back ground color of cells which will come under a specific criteria. Say, cells which contained the date value of current month. I know how to find a specific range of cells. But i don't know how to change the color programatically.  


My Contributions

UDF Link Viewer   --- A tool to visit the links of some most important UDFs 

 Includer_2  ----- A tool to type the #include statement automatically 

 Digits To Date  ----- date from 3 integer values

PrintList ----- prints arrays into console for testing.

 Alert  ------ An alternative for MsgBox 

 MousePosition ------- A simple tooltip display of mouse position

GRM Helper -------- A littile tool to help writing code with GUIRegisterMsg function

Access_UDF  -------- An UDF for working with access database files. (.*accdb only)

 

Share this post


Link to post
Share on other sites
Hello,
 
Hope this helps:
 
#include <EXCEL.AU3>
Local $oAppl = _Excel_Open(True, False, True, True, True)
Local $oWorkbook = _Excel_BookNew($oAppl, 1)

With $oAppl.ActiveWorkbook.Sheets(1)
.Range("A1:A1").Interior.Color = 0x330099
EndWith

 

 
Please note that color codes in excel are BGR and not RGB
 
Other fun commands:
.Range("A1:A1").Font.Bold = True
.Range("A1:A1").Font.Color = 0xFFFFFF
.Range("A1:A1").Borders.Color = 0x000000
Edited by Kinshima

Share this post


Link to post
Share on other sites
@Kinshima

Wow.. Thanks a  lot. Let me try this. :)


My Contributions

UDF Link Viewer   --- A tool to visit the links of some most important UDFs 

 Includer_2  ----- A tool to type the #include statement automatically 

 Digits To Date  ----- date from 3 integer values

PrintList ----- prints arrays into console for testing.

 Alert  ------ An alternative for MsgBox 

 MousePosition ------- A simple tooltip display of mouse position

GRM Helper -------- A littile tool to help writing code with GUIRegisterMsg function

Access_UDF  -------- An UDF for working with access database files. (.*accdb only)

 

Share this post


Link to post
Share on other sites

@Kinshima,

Yes, it worked. And now i am searching for a BGR color chart


My Contributions

UDF Link Viewer   --- A tool to visit the links of some most important UDFs 

 Includer_2  ----- A tool to type the #include statement automatically 

 Digits To Date  ----- date from 3 integer values

PrintList ----- prints arrays into console for testing.

 Alert  ------ An alternative for MsgBox 

 MousePosition ------- A simple tooltip display of mouse position

GRM Helper -------- A littile tool to help writing code with GUIRegisterMsg function

Access_UDF  -------- An UDF for working with access database files. (.*accdb only)

 

Share this post


Link to post
Share on other sites

Warning.  Old thread but its the fist one I found that mentions the swap.

 

Can anyone tell me why the swap is needed.     I've been using ColorIndex and just tried Color instead to get a better shade.   In playing around before coming here I discovered the need for the swap.  It works .... buy why?

_ColorSetRGB()

says RGB in the help file but only gives me the results I want if I use BGR when I use the result in

.Range('A1').Interior.Color

 

Am I misreading the help file or misreading the Microsoft site?
It works fine but I don't know why.

I was reading from here.
https://msdn.microsoft.com/en-us/library/cc296089(office.12).aspx

Its not 2007.  I'm using office 365 but the code seems to be the same.  Is this the difference?  Does newer excel versions need it backwards.   That would be odd.  I'm sure its something else I overlooked.

 

Edited by cal

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

  • Similar Content

    • By Rhidlor
      Quick question, when working with Excel, does each workbook require its own Excel instance, or can multiple workbooks be opened off of the same Excel instance? I tested both ways, on the surface they both seemed to work, just thought I'd ask here to make sure before proceeding any further. Thanks!
      Disclaimer: The following is pseudo code
      $excel_instance1 = _Excel_Open() $workbook1 = _Excel_BookOpen($excel_instance1, @ScriptDir & "\book1.xlsx") $workbook2 = _Excel_BookOpen($excel_instance1, @ScriptDir & "\book2.xlsx") Or
      $excel_instance1 = _Excel_Open() $workbook1 = _Excel_BookOpen($excel_instance1, @ScriptDir & "\book1.xlsx") $excel_instance2 = _Excel_Open() $workbook2 = _Excel_BookOpen($excel_instance2, @ScriptDir & "\book2.xlsx")  
    • By TheDcoder
      Hello everyone, I am working on a project which requires reading a few values from Excel, the catch is that I need it to be very fast... unfortunatley I found out that read operations using the supplied Excel UDF are very slow, more than 150 ms for each operation on average
      Here is my testing setup that I made:
      #include <Excel.au3> #include <MsgBoxConstants.au3> Global $iTotalTime = 0 Test() Func Test() Local $oExcel = _Excel_Open() Local $oBook = _Excel_BookAttach("Test.xlsx", "FileName", $oExcel) Local $sSheet = "Sheet1" If @error Then Return MsgBox($MB_ICONERROR, "Excel Failed", "Failed to attach to Excel") Local $iNum For $iRow = 1 To 6 Time() Local $iNum = Number(_Excel_RangeRead($oBook, $sSheet, "A" & $iRow)) If ($iNum = 1) Then ConsoleWrite("Row " & $iRow & " is 1 and value of column B is " & _Excel_RangeRead($oBook, $sSheet, "B" & $iRow)) Else ConsoleWrite("Row " & $iRow & " is not 1") EndIf ConsoleWrite(". Reading took: ") Time() Next ConsoleWrite("The whole operation took " & $iTotalTime & " milliseconds." & @CRLF) EndFunc Func Time() Local Static $hTimer Local Static $bRunning = False If $bRunning Then Local $iTime = Round(TimerDiff($hTimer), 2) $iTotalTime += $iTime ConsoleWrite($iTime & @CRLF) Else $hTimer = TimerInit() EndIf $bRunning = Not $bRunning EndFunc And Test.xlsx in CSV format:
      1,-1 -1,1 1,-1 1,1 -1,-1 1,1 Here is the actual xlsx but it should expire in a week: https://we.tl/t-EVkxGp1kc6
      And finally output from my script:
      Row 1 is 1 and value of column B is -1. Reading took: 276.06 Row 2 is not 1. Reading took: 163.36 Row 3 is 1 and value of column B is -1. Reading took: 302.58 Row 4 is 1 and value of column B is 1. Reading took: 294.65 Row 5 is not 1. Reading took: 152.33 Row 6 is 1 and value of column B is 1. Reading took: 284.92 The whole operation took 1473.9 milliseconds.  
      Taking ~1.5 seconds for reading 6 rows of data is bad for my script, which needs to run as fast as possible . It would be nice if I can bring this down to 100 ms somehow, I am not very experienced working with MS office so I thought about asking you folks for help and advice on how I can optimize my script to squeeze out every bit of performance that I can get from this script
       
      Thanks for the help in advance!
    • By SlackerAl
      I have been using some AutoIt scripts to manipulate Excel for a few weeks now. Today they stopped working. I have rebooted the PC and I'm not aware of any other significant changes. I can start and use Excel conventionally without a problem, but any attempt to create an excel object from AutoIt fails. E.g
      #include <MsgBoxConstants.au3> #include <Excel.au3> Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Open Example 1", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Produces:
      @error = 1
      @extended = -2147221005
      I can't find that COM error listed anywhere... Anyone have any ideas?
    • By Eggie6
      Hi,
      I'm trying to write an formula to the excel, but it actually does not write anything into the cell, any ideas?
      $formula1="=MID(B"&$utakmice&",SEARCH("-",B"&$utakmice&")-1,1)" _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $formula1, "C"&$smth)  
    • By XinYoung
      Hello all,
      Preface: Column A is full of courses. Column B is full of usernames. If course (A1) exists, we check to see if username (B1) is enrolled. If user is found, the user is un-enrolled. Then Loop.
      I am working on a script that loops through an Excel file and pastes the content from A1 into a textbox in IE, does some stuff, then pastes the content from B1 into a different textbox. Then it loops around until all used rows in columns A and B have been accounted for.
      For some reason, column A loops properly but column B doesn't. B1 is pasted over and over again. So, as it loops, B1 is constantly being pasted, first accompanying A1's loop, then A2, and so on.
      The OpenExcel() func opens the Excel file the user specifies in an earlier function. It's supposed to gather the entire used range of columns A and B. The SearchCourse() func only uses column A, pasting its content into a Search tool in IE. This seems to be working fine. SearchResult() puts "Course Not Found" into column C if the search fails. If the search is successful, however, we move onto... EnterCourse(). This simply gets us to the place where column B's content comes into play. UnenrollNow(). Here, we paste the variable $_userName into a textbox. I don't know why it's always B1  Func OpenExcel() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, True, True) $oExcel.Sheets("CopyCourses").Activate ;~ Get all used cells in columns A and B $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:B")) ;~ Create the $aSearchResult array ReDim $aSearchResult[UBound($aSearchItems)] ;~ Loop through the array starting at 0 until the end of the array which is (Ubound($aSearchItems) - 1) For $i = 0 To UBound($aSearchItems) - 1 ;~ Column 0 ConsoleWrite($aSearchItems[$i][0] & @CRLF) ;~ Column 1 ConsoleWrite($aSearchItems[$i][1] & @CRLF) $aSearchResult[$i] = SearchCourse($aSearchItems[$i][0], $aSearchItems[$i][1]) Next _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") ;~ When the loop is complete, run the Finished function. Finished() EndIf EndFunc ;==>OpenExcel Func SearchCourse($_sSearchResult, $_userName) If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else _IENavigate($oIE, $urlBBCourseSearch) _IELoadWait($oIE) Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch") Local $oSearchString = _IEFormElementGetObjByName($oForm, "courseInfoSearchText") _IEFormElementSetValue($oSearchString, $_sSearchResult) _IEFormSubmit($oForm) _IELoadWait($oIE) Local $oBBTable = _IETableGetCollection($oIE, 2) $aBBTableData = _IETableWriteToArray($oBBTable) Return SearchResult() EndIf EndFunc ;==>SearchCourse Func SearchResult() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $sResult $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0) If $iSearchIndex = -1 Then $sResult = "Course Not Found" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Else EnterCourse() $sResult = "UnEnrolled!" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") EndIf Return $sResult EndIf EndFunc ;==>SearchResult Func EnterCourse() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $clickFail = "DATE CREATED" _IELinkClickByIndex($oIE, 34) _IELoadWait($oIE) $sourceCode = _IEBodyReadHTML($oIE) If StringInStr($sourceCode, $clickFail) <> 0 Then _IELinkClickByIndex($oIE, 35) _IELoadWait($oIE) EndIf $oLink = _IEGetObjById($oIE, "controlpanel.users.and.groups_groupExpanderLink") _IEAction($oLink, "click") Sleep(500) _IELinkClickByText($oIE, "Users") _IELoadWait($oIE) $aSearchResult[$i] = UnenrollNow($aSearchItems[$i][0], $aSearchItems[$i][1]) EndIf EndFunc ;==>EnterCourse Func UnenrollNow($_sourceCourseId, $_userName) If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $UserError = "No users found" Local $sResult $criteriaUsername = _IEGetObjById($oIE, "userInfoSearchKeyString") _IEFormElementOptionSelect($criteriaUsername, 0, 1, 'byIndex') $criteriaUsername = _IEGetObjById($oIE, "userInfoSearchOperatorString") _IEFormElementOptionSelect($criteriaUsername, 0, 1, 'byIndex') ;Paste whats copied from column B into the Username text box. Local $oForm = _IEGetObjByName($oIE, "userManagerSearchForm") Local $oSearchString = _IEFormElementGetObjByName($oForm, "userInfoSearchText") ;PROBLEM HERE... _IEFormElementSetValue($oSearchString, $_userName) ;^^^^^^ WHY IS $_userName ALWAYS B1 ??? Sleep(1000) _IEFormSubmit($oForm) _IELoadWait($oIE) $sourceCode = _IEBodyReadHTML($oIE) If StringInStr($sourceCode, $UserError) <> 0 Then $sResult = "User Not Found" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Return $sResult Else MsgBox(0, "Unenrollment READY!", "We're ready to unenroll foreal") $sResult = "DUN" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Return $sResult EndIf EndIf EndFunc ;==>UnenrollNow Please let me know if any further information is needed.
      If you see other problems or redundancies in my code, please let me know.
      Thank you!
       
×
×
  • Create New...