Recent Post

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


0 comments:

Post a Comment

GAReddy @ OneTestingCenter @ All Articles