QTP Excel Comparision

[gautamwin]

Hi Can any one able to help me in below logic?

I have two folders containing multiple excel reports.

Say Eg: C:\Prereports and D:\PostReports are folders

C:\Preports
1. One.xls
2. Two.xls

D:\PostReports
1. One.Xls
2. Two.Xls


I need to compare the one.xls available in C:\Prereports with one.xls available in D:\PostReports. The file count may change every time and also each file has mutiple sheets. need to compare all of them available in the path


Can we do this in QTP?

[basanth27]

Yes. The logic would be something like below,

1. Read row 1 from one.xls in C Drive
2. With this row 1 data query [ Select * From Sheetname
] the one.xls on D drive and check if that value exists if it does pass it else fail.

However, there are other ways to do it as well. You may google for Excel comparision for sample codes.
If the data is large then i recommend you script it in perl using the Hash table. It is efficient and handles the value and key pair for large data very cohesively.

[Novemberrain81]

Do the following. Create excelobject then create one object for each workbooks. Then find the row and column count for both of the files. If those counts do not match then the data aren't equal. Either way do a for loop and you compare data cell by cell.
Do a nested for loop if you want it to go left to right...IE
(1,1)(1,2)(1,3) so on

Code:

usedrowoflist1 = objWSlist1.Usedrange.Rows.Count
For i=2 to usedrowoflist1
    For j=1 to usedcolumnoflist1
            If objWSlist1.cells(i, j) = objWSlist2.cells(i,j) Then
                                Do whatever you have to do if passes.
                    Else  
                                Do whatever you have to do if fails
                        End If
        Next
Next