QTP - Excel Comparision

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

' 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 Comparision