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
On Wednesday, September 11, 2013 5:10:09 AM UTC-4, 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.
