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
On Wednesday, September 18, 2013 1:07:33 AM UTC-4, Mary wrote:
> 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.
>
> On Wednesday, September 11, 2013 2:40:09 PM UTC+5:30, Mary wrote:
>>
>> 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
>>
>
--
--
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 [email protected]
To unsubscribe from this group, send email to
[email protected]
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 [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
' comparing_two_excel_spreadsheets
Set xLApp_1 = CreateObject("Excel.Application")
xlApp_1.visible = True
Set xlWB_1 = xlApp_1.workbooks.Open
("C:\vbscript_Practice\Excel\Compare_Book1.xls")
print "number of worksheets = " & xlWB_1.worksheets.count
For each shtName in xlWB_1.WorkSheets
print shtName.name
Next
' 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
print "number of rows = " & nrows & " :: number of columns = " & ncolumns
' create a new column A and autofill from 1 to the number of rows
' Column A will the the key for a dictionary
' I like using the dictionaries in dot net
' for this case, I will use the ListDictionary
' using dotnetfactory as I find it easier to use the Enumerator then throwing
the keys into an array
Set dict_1 =
dotnetfactory.CreateInstance("System.Collections.Specialized.ListDictionary")
Set myEnum_1 =
DotnetFactory.CreateInstance("System.Collections.IDictionaryEnumerator")
' the values will be the concatenation of the other columns. I do not know
what the maximum string size might be.
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
Next
'' uncomment to verify the dictionary
'Set myEnum_1 = dict_1.GetEnumerator
'While myEnum_1.MoveNext
' print myEnum_1.key & " :: " & myEnum_1.value
'Wend
' opening the second Excel object
Set xLApp_2 = CreateObject("Excel.Application")
'Set xlApp_2 = Eval("GetObject(,""Excel.Application"")")
xlApp_2.visible = True
Set xlWB_2 = xlApp_2.workbooks.Open
("C:\vbscript_Practice\Excel\Compare_Book2.xls")
print "number of worksheets = " & xlWB_2.worksheets.count
'' print the names in the second workbook if needed
'For each shtName in xlWB_2.WorkSheets
' print shtName.name
'Next
' get the activeSheet name
'print "active sheet = " & xlApp_2.ActiveSheet.Name
'Set xlActiveSheet_2 = xlWB_2.activeSheet
'print xlActiveSheet_2.name
' I am assigning the sheetName to be Sheet1
Set sheetName_2 = xlWB_2.Sheets("Sheet1")
nrows = sheetName_2.usedrange.rows.count
ncolumns = sheetName_2.usedrange.columns.count
print "number of rows = " & nrows & " :: number of columns = " & ncolumns
' creating the second dictionary
Set dict_2 =
dotnetfactory.CreateInstance("System.Collections.Specialized.ListDictionary")
Set myEnum_2 =
DotnetFactory.CreateInstance("System.Collections.IDictionaryEnumerator")
' adding the keys and values
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
Next
Set myEnum_2 = dict_2.GetEnumerator
'While myEnum_2.MoveNext
' print myEnum_2.key & " :: " & myEnum_2.value
'Wend
Set myEnum_1 = dict_1.GetEnumerator
Set myEnum_2 = dict_2.GetEnumerator
rownum = 1
While myEnum_1.MoveNext
myEnum_2.MoveNext
print myEnum_1.Key & "::" & myEnum_1.value
If myEnum_1.value = myEnum_2.value Then
' do nothing
Else
' the wait may not be needed??
'wait(1)
' setting the color to red in column 1 if the dictionary values
are not equal
' only highlighting problems in the second workbook.
' if you need both workbooks highlighed, modify as needed
xlApp_2.Sheets("Sheet1").cells(rownum,1).Select
xlApp_2.cells(rownum,1).Interior.ColorIndex = 3
' print "found a problem"
' you can see that somewhere, there is a mismatch
print myEnum_1.value & " :: " & myEnum_2.value
' would like to highlight the individual cell that differs from
book1
' create two arrays and split the values on the symbols used in
building the string value
' arr_1 is from book1
' arr_2 is from book2
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
Compare_book1
1 name1 job1 salary1
2 name2 job2 salary2
3 name3 job3 salary3
4 name4 job4 salary4
5 name5 job5 salary5
6 name6 job6 salary6
7 name7 job70 salary7
8 name8 job8 salary8
9 name9 job9 salary9
10 name101 job10 salary10
11 name11 job11 salary11
12 name12 job12 salary120
13 name13 job13 salary13
14 name14 job14 salary14
15 name15 job15 salary15
16 name16 job16 salary16
17 name17 job17 salary17
18 name18 job18 salary18
19 name19 job19 salary19
20 name20 job20 salary20
compare_book2
1 name1 job1 salary1
2 name2 job2 salary2
3 name300 job3 salary3
4 name4 job4 salary4
5 name5 job5 salary5
6 name6 job6 salary6
7 name7 job7 salary7
8 name8 job8 salary8
9 name9 job9 salary9
10 name10 job10 salary10
11 name11 job11 salary11
12 name12 job12 salary12
13 name13 job13 salary13
14 name14 job14 salary14
15 name15 job15 salary15
16 name16 job16 salary16
17 name17 job17 salary17
18 name18 job18 salary18
19 name19 job19 salary19
20 name20 job20 salary20
