Excel Comparison Scripts


Excel Comparison Scripting Examples for UFT (formerly QTP)

'One to One Binary Comparison (2 Excel columns)


Option Explicit

Dim objExcel, objWorkbook, objWorksheet, RecordCount, i, j, Buttons

Dim oButton, Expected, Actual, k

Set objExcel = createObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and Settings\Administrator\Desktop\input.xls")

Set objWorksheet = objWorkbook.Worksheets(1)



Set oButton = Description.Create

oButton("micclass").value = "WinButton"

SystemUtil.Run "C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe"

Set Buttons = Dialog("text:=Login").ChildObjects(oButton)



objWorksheet.Cells(1, 1) = "Expected"

objWorksheet.Cells(1, 2) = "Actual"

objWorksheet.Cells(1, 3) = "Result"



For i = 0 To Buttons.Count-1 Step 1

            objWorksheet.cells(i+2, 2) = Buttons(i).GetROProperty("text")

Next



RecordCount = objWorksheet.usedrange.rows.count



For k = 2 To RecordCount Step 1

            Expected = objWorksheet.Cells(k, 1)

            Actual = objWorksheet.Cells(k, 2)

           

            If Expected = Actual Then

                        objWorksheet.Cells(k, 3) = "Passed"

            Else

                        objWorksheet.Cells(k, 3) = "Failed"

            End If

Next

Dialog("text:=Login").Close

objWorkbook.Save

objWorkbook.Close

objExcel.Quit



Set objWorksheet = Nothing

Set objWorkbook = Nothing

Set objExcel = Nothing

---------------------------------------------------------------------

'One to One Textual comparison (2 Excel columns)


Option Explicit

Dim objExcel, objWorkbook, objWorksheet, RecordCount, i, j, Buttons

Dim oButton, Expected, Actual, k

Set objExcel = createObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and Settings\Administrator\Desktop\input.xls")

Set objWorksheet = objWorkbook.Worksheets(1)



Set oButton = Description.Create

oButton("micclass").value = "WinButton"

SystemUtil.Run "C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe"

Set Buttons = Dialog("text:=Login").ChildObjects(oButton)



objWorksheet.Cells(1, 1) = "Expected"

objWorksheet.Cells(1, 2) = "Actual"

objWorksheet.Cells(1, 3) = "Result"



For i = 0 To Buttons.Count-1 Step 1

            objWorksheet.cells(i+2, 2) = Buttons(i).GetROProperty("text")

Next



RecordCount = objWorksheet.usedrange.rows.count



For k = 2 To RecordCount Step 1

            Expected = objWorksheet.Cells(k, 1)

            Actual = objWorksheet.Cells(k, 2)

           

            If StrComp(Expected, Actual, 1) =0 Then

                        objWorksheet.Cells(k, 3) = "Passed"

            Else

                        objWorksheet.Cells(k, 3) = "Failed"

            End If

Next

Dialog("text:=Login").Close

objWorkbook.Save

objWorkbook.Close

objExcel.Quit



Set objWorksheet = Nothing

Set objWorkbook = Nothing

Set objExcel = Nothing

-----------------------------------------------------

'Many to Many Textual comparison (2 Excel columns)


Option Explicit

Dim objExcel, objWorkbook, objWorksheet, RecordCount, i, j, Buttons

Dim oButton, Expected, Actual, k, Flag

Set objExcel = createObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and Settings\Administrator\Desktop\input.xls")

Set objWorksheet = objWorkbook.Worksheets(1)



Set oButton = Description.Create

oButton("micclass").value = "WinButton"

SystemUtil.Run "C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe"

Set Buttons = Dialog("text:=Login").ChildObjects(oButton)



objWorksheet.Cells(1, 1) = "Expected"

objWorksheet.Cells(1, 2) = "Actual"

objWorksheet.Cells(1, 3) = "Result"



For i = 0 To Buttons.Count-1 Step 1

            objWorksheet.cells(i+2, 2) = Buttons(i).GetROProperty("text")

Next



RecordCount = objWorksheet.usedrange.rows.count



For j = 2 To RecordCount Step 1

            Expected = objWorksheet.Cells(j, 1)

           

            For k = 2 To RecordCount Step 1

                        Actual = objWorksheet.Cells(k, 2)



                        If StrComp(Expected, Actual, 1) =0 Then

                        Flag = 0

                        Exit For

                        Else

                        Flag = 1

                        End If

                        Next

            If Flag =0 Then

                        objWorksheet.cells(j, 3) = "Passed"

                        Else

                        objWorksheet.cells(j, 3) = "Failed"

            End If

Next

Dialog("text:=Login").Close

objWorkbook.Save

objWorkbook.Close

objExcel.Quit



Set objWorksheet = Nothing

Set objWorkbook = Nothing

Set objExcel = Nothing
---------------------------------------------------------------

0 comments:

Post a Comment