rajkumar24 Posted August 20, 2009 Share Posted August 20, 2009 How to compare two excel files when there is a difference in color for the content of files. Link to comment Share on other sites More sharing options...
zfisherdrums Posted August 21, 2009 Share Posted August 21, 2009 Some help to aid in your journey: In Excel: Record a macro while changing color of a cell/range Observe the code written in the macro. In AutoIt: Create an instance of Excel: Use the code you observed in Excel to determine the color of a cell/range Dim $oXl = ObjGet( "", "Excel.Application" ) Func _ExcelGetRangeColor( $oExcel, $sAddress ) ; beware: sending only rc coordinates assumes the Activesheet context. ; To ensure that the correct sheet is assumed, pass in the sheet name: ; ; example: $sAddress = "Sheet1!A1" Return $oExcel.Application.Range($sAddress). ; <---- finish this line with the property you observed in Excel EndFunc Dim $result = _ExcelGetRangeColor( $oXl, "Sheet1!A1" ) ConsoleWrite( $result & @CRLF) Successfully completing this should show you how to leverage the Excel DOM ( and generated macro code ) in AutoIT. Identify .NET controls by their design time namesLazyReader© could have read all this for you. Unit Testing for AutoItFolder WatcherWord Doc ComparisonThis here blog... Link to comment Share on other sites More sharing options...
rajkumar24 Posted August 21, 2009 Author Share Posted August 21, 2009 Thanx zfisherdrums, I need some more help.I want to compare two excel files.I want the difference when there is a difference in color in cell. Example:-In one excel the cell is filled with Red and in another excel the cell is filled with Green.So there is a difference in color.So how can i compare. Link to comment Share on other sites More sharing options...
rajkumar24 Posted August 21, 2009 Author Share Posted August 21, 2009 Hi...Please find the 2 attachment. In the two excel file,colors for the cells are different.So how can i compare and how can i get the difference report. Is this possible in AUTO IT. File1:- Cell color 1234 red 245 green 3534 yellow File2:- Cell color 1234 yellow 245 red 3534 green Link to comment Share on other sites More sharing options...
Manjish Posted August 21, 2009 Share Posted August 21, 2009 pixelgetcolor() would be your best option here.. Open the excel file in visible mode and check the color of cells [font="Garamond"]Manjish Naik[/font]Engineer, Global Services - QPSHoneywell Automation India LimitedE-mail - Manjish.Naik@honeywell.com Link to comment Share on other sites More sharing options...
99ojo Posted August 21, 2009 Share Posted August 21, 2009 pixelgetcolor() would be your best option here.. Open the excel file in visible mode and check the color of cells Hi, not elegant: expandcollapse popup#include <excel.au3> Global $aArrayColor [1] [1] Global $bArrayColor [1] [1] $excel1 = @UserProfileDir & "\Eigene Dateien\mappe1.xls" ;change to your excelfiles $excel2 = @UserProfileDir & "\Eigene Dateien\mappe2.xls" ;change to your excelfiles $oExcel1 = _ExcelBookOpen ($excel1, 0) ;open 1st ExcelBook $aArray = _ExcelReadSheetToArray ($oExcel1) ;reading Excelsheet to Array to get dimensions ReDim $aArrayColor [$aArray [0] [0] + 1] [$aArray [0] [1] + 1] ;redim 1st array for colorstoring ;Get color for 1st For $i = 1 To $aArray [0] [0] For $j = 1 To $aArray [0] [1] $aArrayColor [$i] [$j] = _ExcelGetRangeColor ($oExcel1, $i, $j) Next Next _ExcelBookClose ($oExcel1, 0) $oExcel1 = _ExcelBookOpen ($excel2, 0) ;open 2nd excelbook $aArray = _ExcelReadSheetToArray ($oExcel1) ReDim $bArrayColor [$aArray [0] [0] + 1] [$aArray [0] [1] + 1] ;redim 2nd array for colorstoring ;Get color for 2nd For $i = 1 To $aArray [0] [0] For $j = 1 To $aArray [0] [1] $bArrayColor [$i] [$j] = _ExcelGetRangeColor ($oExcel1, $i, $j) Next Next _ExcelBookClose ($oExcel1, 0) ;Compare the two color Arrays For $i = 0 To UBound ($aArrayColor) - 1 For $j = 0 To UBound ($aArrayColor, 2) - 1 If $aArrayColor [$i] [$j] <> $bArrayColor [$i] [$j] Then MsgBox (0,"", "Row " &$i & " "& "Column " & $j & " different color!") EndIf Next Next Func _ExcelGetRangeColor( $oExcel, $sRow, $sColumn ) ; beware: sending only rc coordinates assumes the Activesheet context. ; To ensure that the correct sheet is assumed, pass in the sheet name: ; ; example: $sAddress = "Sheet1!A1" Return $oExcel.Activesheet.Cells($sRow, $sColumn).Interior.ColorIndex EndFunc ;-)) Stefan Link to comment Share on other sites More sharing options...
rajkumar24 Posted August 21, 2009 Author Share Posted August 21, 2009 Thanks Stefan and Manjish...... To Stefan:- What I need to define for the user defined functions like _ExcelBookOpen _ExcelReadSheetToArray _ExcelBookClose Link to comment Share on other sites More sharing options...
Manjish Posted August 21, 2009 Share Posted August 21, 2009 (edited) hi these functions are already included in excel.au3 udf file.. its included in v 3.3.0.0.. If u r using lesser version pls download this file by searching in the forums Edited August 21, 2009 by Manjish [font="Garamond"]Manjish Naik[/font]Engineer, Global Services - QPSHoneywell Automation India LimitedE-mail - Manjish.Naik@honeywell.com 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