how to compare excel files?

[Mary]

I need to compare two excel files (each file has 20 sheets).
I used the below code. But it is taking more than 1 hour to complete. Please share if any other way to compare excels.

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open(“Docs1.xls”)
Set objWorkbook2= objExcel.Workbooks.Open(“Docs2.xls”)

Set objWorksheet1= objWorkbook1.Worksheets(1)

Set objWorksheet2= objWorkbook2.Worksheets(1)

   For Each cell In objWorksheet1.UsedRange
       If cell.Value <> objWorksheet2.Range(cell.Address).Value Then
           cell.Interior.ColorIndex = 3′Highlights in red color if any changes in cells
       Else
           cell.Interior.ColorIndex = 0
       End If
   Next

set objExcel=nothing

[Arunabh Ray]

If you want to know which cells are different, this is the only way...

but if you just want to know that if they are different then compare the file sizes, if they are differ they will have different file sizes.

The best thing would be to to compare the file sizes first, then the individual cells.

--
--
You received this message because you are subscribed to the Google
"QTP - HP Quick Test Professional - Automated Software Testing"
group.
To post to this group, send email to Mercu...@googlegroups.com
To unsubscribe from this group, send email to
MercuryQTP+...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/MercuryQTP?hl=en
 
---
You received this message because you are subscribed to the Google Groups "QTP - HP Quick Test Professional - Automated Software Testing" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mercuryqtp+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--

Regards

Arunabh Ray

[Mary]

Thanks Arunabh,
      But in my scenario - comparing file size is not enough. I should compare each cell's value against the other file. Please advice me on this.

[Parke]

Mary:

 

Is it safe to assume that the excel sheets you are comparing have the same number of rows and columns?

 

I am assuming that most of the time, the cells match, so most of the time, a cell by cell comparison is a waste of time. (hmmm, a lot of time in that sentence.)

 

The code assumes that column A is a unique key and is the same in both workbooks.  I like to use the dictionaries from Dot Net as they are much improved over the dictionary object in vbscript.

 

In the code, I am only comparing “Sheet1” in each workbook.

 

There is more coding than in the original method but I believe the time to complete the comparisons should be much less.  Please let me know if I am correct.

 

Thanks,

 

Parke

[Parke]

Mary:

 

I setup two spreadsheets with 6 columns, 500 rows and had 12 differences between the two spreadsheets.

 

I ran Mary’s script with and without the Else statement and the code using dictionaries.  The code is pasted below:

Results are an average of six runs:

Compare every cell		Compare every cell		Using dictionary
with else statement		removing else statement
60.7 sec		39.4 sec		35.2 sec

 

The most surprising result for me was the increase in speed when the else statement was removed from the code.

Using the dictionary method to find cells that are not the same between the two spreadsheets is slightly faster than using the standard QTP method for comparing two spreadsheets.

' comparing two_excel_spreadsheets using ListDictionary:

startTime = Timer
Set xLApp_1 = CreateObject("Excel.Application")
xlApp_1.visible = True
Set xlWB_1 = xlApp_1.workbooks.Open    ("C:\vbscript_Practice\Excel\Compare_Book1.xls")

' setting the sheet name to Sheet1
Set sheetName_1 = xlWB_1.Sheets("Sheet1")
nrows = sheetName_1.usedrange.rows.count
ncolumns = sheetName_1.usedrange.columns.count

Set dict_1 = dotnetfactory.CreateInstance("System.Collections.Specialized.ListDictionary")
Set myEnum_1 = DotnetFactory.CreateInstance("System.Collections.IDictionaryEnumerator") 

For i = 1 to nrows
    dict_1.add sheetName_1.cells(i,1).value, sheetName_1.cells(i,2).value & ";:" & sheetName_1.cells(i,3).value & ";:" & sheetName_1.cells(i,4).value &";:" & sheetName_1.cells(i,5).value & ";:" & sheetName_1.cells(i,6).value
Next

Set xLApp_2 = CreateObject("Excel.Application")
xlApp_2.visible = True
Set xlWB_2 = xlApp_2.workbooks.Open    ("C:\vbscript_Practice\Excel\Compare_Book2.xls")
Set sheetName_2 = xlWB_2.Sheets("Sheet1")
Set dict_2 = dotnetfactory.CreateInstance("System.Collections.Specialized.ListDictionary")
Set myEnum_2 = DotnetFactory.CreateInstance("System.Collections.IDictionaryEnumerator") 

For i = 1 to nrows
    dict_2.add sheetName_2.cells(i,1).value, sheetName_2.cells(i,2).value & ";:" & sheetName_2.cells(i,3).value & ";:" & sheetName_2.cells(i,4).value&";:" & sheetName_2.cells(i,5).value & ";:" & sheetName_2.cells(i,6).value
Next

Set myEnum_2 = dict_2.GetEnumerator
Set myEnum_1 = dict_1.GetEnumerator
Set myEnum_2 = dict_2.GetEnumerator
rownum = 1
While myEnum_1.MoveNext
    myEnum_2.MoveNext
    If myEnum_1.value <> myEnum_2.value Then
        arr_1 = split(myEnum_1.value,";:")
        arr_2 = split(myEnum_2.value,";:")
        For j = 0 To uBound(arr_1)
            ' find which array elements do not match and adjust the background/interior color of that cell
            If arr_1(j) <> arr_2(j) Then
                xlApp_2.Cells(rownum,j+2).Interior.ColorIndex = 5 '' blue
            End If
        Next
    End If
    rownum = rownum + 1
Wend

endTime = timer
totalTime = endTime - startTime
print "time to run = " & totalTime

 

hth,

 

Parke

[Muhammad Kamran]

Can someone send same code in C sharp . I am in situation to compare two 
Excel filed in my project . It's very urgent .

Appreciated in advance 
Thanks, 

Muhammad kamran

[Synkronizer Excel Compare]

Hi Mary,

Use Some Dedicated Excel Tool Like Synkronizer Excel Add-in. Using This Tool You can Compare Excel Files within just a Few Clicks.

https://www.synkronizer.com/compare-excel-tables-features/compare