Sign in to follow this  
Followers 0
rajkumar24

Excel File Comparision

8 posts in this topic

How to compare two excel files when there is a difference in color for the content of files.

Share this post


Link to post
Share on other sites



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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

pixelgetcolor() would be your best option here.. Open the excel file in visible mode and check the color of cells

Hi,

not elegant:

#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

Share this post


Link to post
Share on other sites

Thanks Stefan and Manjish......

To Stefan:-

What I need to define for the user defined functions like

_ExcelBookOpen

_ExcelReadSheetToArray

_ExcelBookClose

Share this post


Link to post
Share on other sites

#8 ·  Posted (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 by Manjish

[font="Garamond"]Manjish Naik[/font]Engineer, Global Services - QPSHoneywell Automation India LimitedE-mail - Manjish.Naik@honeywell.com

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