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 SettingsAdministratorDesktopinput.xls”)

 

Set objWorksheet = objWorkbook.Worksheets(1)

 

 

Set oButton = Description.Create

 

oButton(“micclass”).value = “WinButton”

 

SystemUtil.Run “C:Program FilesHPUnified Functional Testingsamplesflightappflight4a.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 SettingsAdministratorDesktopinput.xls”)

 

Set objWorksheet = objWorkbook.Worksheets(1)

 

 

Set oButton = Description.Create

 

oButton(“micclass”).value = “WinButton”

 

SystemUtil.Run “C:Program FilesHPUnified Functional Testingsamplesflightappflight4a.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 SettingsAdministratorDesktopinput.xls”)

 

Set objWorksheet = objWorkbook.Worksheets(1)

 

 

Set oButton = Description.Create

 

oButton(“micclass”).value = “WinButton”

 

SystemUtil.Run “C:Program FilesHPUnified Functional Testingsamplesflightappflight4a.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
—————————————————————

 

 

Comments