QTP - Excel - Excel Comparison

QTP - Excel - Excel Comparison
How to compare two excel sheets (Expected and Actual Sheets)

'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

' GAReddy @ OneTestingCenter @ QTP @ Excel Comparison                   

'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

ExcelFilePath="D:\GAReddy\OneTestingCenter\QTP\TestData\"

ExpectedExcelBook=" D:\GAReddy\OneTestingCenter\QTP\TestData\QACompleteLearningReferense@AllOne.xls"

ActualExcelBook=" D:\GAReddy\OneTestingCenter\QTP\TestData\QACompleteLearningReferense@AllOne1.xls"

DifferenseSheet="MyExcelSheet.xls"

ActualSheet="Automation Testing"

ExpectedSheet="Automation Testing"

Call ExcelCompare(ExcelFilePath,ExpectedExcelBook,ActualExcelBook,DifferenseSheet,ActualSheet,ExpectedSheet)

Function ExcelCompare(ExcelFilePath,ExpectedExcelBook,ActualExcelBook,DifferenseSheet,ActualSheet,ExpectedSheet)

                                Set oExcel=Createobject("Excel.Application")

                                Set oExpExcel=Createobject("Excel.Application")

                                Set oActExcel=Createobject("Excel.Application")

                                Set NewSheet = oExcel.Application.Workbooks.Add

                                Set MySheet=oExcel.Application.Activeworkbook.Worksheets("sheet1")

                                               

                                oActExcel.Workbooks.Open ActualExcelBook,2

                                oExpExcel.Workbooks.Open ExpectedExcelBook,2

                Set oExpSheet=oExpExcel.Application.Activeworkbook.Worksheets(ExpectedSheet)

                Set oActSheet=oActExcel.Application.Activeworkbook.Worksheets(ActualSheet)

                                                               

                                RowsCount =  oExpSheet.UsedRange.Rows.count

                                ColsCount =           oExpSheet.UsedRange.Columns.count

                                RowsCount1=  oActSheet.UsedRange.Rows.count

                                ColsCount1=         oActSheet.UsedRange.Columns.count

                                For i=1 to RowsCount

                                                For j=1 to ColsCount

                                                 If  Trim(oExpSheet.Cells(i,j))<>Trim(oActSheet.Cells(i,j))Then

                                MySheet.Cells(i,j)=oExpSheet.cells(i,j) &"***"& vbtab & oActSheet.cells(i,j)

                                                                MySheet.Cells(i,j).Interior.Colorindex=34

                                                                else

                                                                MySheet.Cells(i,j)=oExpSheet.Cells(i,j)

                                                                End If

                                                Next

                                Next

                                MySheet.SaveAs ExcelFilePath&DifferenseSheet

                                oExcel.Quit

                                oExpExcel.Quit

                                oActExcel.Quit

                                Set oExcel=Nothing

                                Set oExpExcel=Nothing

                                Set oActExcel=Nothing

End Function

Posted in: QTP - Excel - Excel Comparison