UFT Class 31
(VBScript Excel Application Operations Part-2)
Excel Application Object (Main Object)
Set Variable = CreateObject(“Excel.Application”)
————————————
Excel Workbook Object (Sub Object)
Set Variable = ExcelApplicationObject.Workbooks.Add/Open(“File path”)
——————————————–
Excel Work sheet object (Sub-sub Object)
Set Variable = ExcelWorkBookObject.Worksheets(Sheet id)
——————————————————–
Examples:
‘Read data from an excel file and perform data driven testing for login functionality.
Dim objExcel, objWorkbook, objWorksheet, RowsCount
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Users\G C Reddy\Desktop\testdata.xlsx”)
Set objWorksheet = objWorkbook.Worksheets(1)
RowsCount = objWorksheet.UsedRange.Rows.Count
For i = 2 To RowsCount Step 1
SystemUtil.Run “C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe”
Dialog(“Login”).Activate
Dialog(“Login”).WinEdit(“Agent Name:”).Set objWorksheet.Cells(i, 1)
Dialog(“Login”).WinEdit(“Password:”).Set objWorksheet.Cells(i, 2)
Wait 2
Dialog(“Login”).WinButton(“OK”).Click
Window(“Flight Reservation”).Close
Next
objExcel.Quit
Set objWorksheet= Nothing
Set objWorkbook= Nothing
Set objExcel= Nothing
‘Read data from an excel file and perform data driven testing for login functionality.
Dim objExcel, objWorkbook, objWorksheet, RowsCount
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Users\G C Reddy\Desktop\testdata.xlsx”)
Set objWorksheet = objWorkbook.Worksheets(1)
RowsCount = objWorksheet.UsedRange.Rows.Count
For i = 2 To RowsCount Step 1
SystemUtil.Run “C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe”
Dialog(“Login”).Activate @@ hightlight id_;_459804_;_script infofile_;_ZIP::ssf1.xml_;_
Dialog(“Login”).WinEdit(“Agent Name:”).Set objWorksheet.Cells(i, 1)
Dialog(“Login”).WinEdit(“Password:”).Set objWorksheet.Cells(i, 2)
Wait 2
Dialog(“Login”).WinButton(“OK”).Click
If Window(“Flight Reservation”).Exist(12) Then
Window(“Flight Reservation”).Close
objWorksheet.Cells(i, 3) = “Login Successful – Passed”
Else
SystemUtil.CloseDescendentProcesses
objWorksheet.Cells(i, 3) = “Login Unsuccessful – Failed”
End If
Next
objWorkbook.Save
objExcel.Quit
Set objWorksheet= Nothing
Set objWorkbook= Nothing
Set objExcel= Nothing
‘Read data from an excel file and perform data driven testing for login functionality.
Dim objExcel, objWorkbook, objWorksheet, RowsCount
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Users\G C Reddy\Desktop\testdata.xlsx”)
Set objWorksheet = objWorkbook.Worksheets(1)
RowsCount = objWorksheet.UsedRange.Rows.Count
For i = 2 To RowsCount Step 1
SystemUtil.Run “C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe”
Dialog(“Login”).Activate
Dialog(“Login”).WinEdit(“Agent Name:”).Set objWorksheet.Cells(i, 1)
Dialog(“Login”).WinEdit(“Password:”).Set objWorksheet.Cells(i, 2)
Wait 2
Dialog(“Login”).WinButton(“OK”).Click
If Window(“Flight Reservation”).Exist(12) Then
Window(“Flight Reservation”).Close
objWorksheet.Cells(i, 3) = “Login Successful – Passed”
Else
objWorksheet.Cells(i, 4) = Dialog(“Login”).Dialog(“Flight Reservations”).Static(“Please enter password”).GetROProperty(“text”)
SystemUtil.CloseDescendentProcesses
objWorksheet.Cells(i, 3) = “Login Unsuccessful – Failed”
End If
Next
objWorkbook.Save
objExcel.Quit
Set objWorksheet= Nothing
Set objWorkbook= Nothing
Set objExcel= Nothing
‘Read Button Names from Login Dialog and export to excel file 2nd sheet.
Dim objExcel, objWorkbook, objWorksheet, oButton, Buttons, i
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Users\G C Reddy\Desktop\testdata.xlsx”)
Set objWorksheet = objWorkbook.Worksheets(2)
Set oButton = Description.Create
oButton(“Class Name”).Value = “WinButton”
Set Buttons= Dialog(“Login”).ChildObjects(oButton)
Msgbox Buttons.Count
objWorksheet.Cells(1, 1) = “Button Names”
For i = 0 To Buttons.Count – 1 Step 1
objWorksheet.Cells(i+2, 1) = Buttons(i).GetRoProperty(“text”)
Next
objWorkbook.Save
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
‘Open 1 to 10 Orders, capture OrderNo and Customer names, export to excel file 3rd sheet.
Dim objExcel, objWorkbook, objWorksheet, oButton, Buttons, i
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Users\G C Reddy\Desktop\testdata.xlsx”)
Set objWorksheet = objWorkbook.Worksheets(3)
objWorksheet.Cells(1, 1) = “Order No”
objWorksheet.Cells(1, 2) = “C_Name”
For i = 1 To 10 Step 1
Window(“Flight Reservation”).Activate
Window(“Flight Reservation”).WinButton(“Button”).Click
Window(“Flight Reservation”).Dialog(“Open Order”).WinCheckBox(“Order No.”).Set “ON”
Window(“Flight Reservation”).Dialog(“Open Order”).WinEdit(“Edit”).Set i
wait 2
Window(“Flight Reservation”).Dialog(“Open Order”).WinButton(“OK”).Click
OrderNo = Window(“Flight Reservation”).WinEdit(“Order No:”).GetROProperty(“text”)
C_Name = Window(“Flight Reservation”).WinEdit(“Name:”).GetROProperty(“text”)
objWorksheet.Cells(i+1, 1) = OrderNo
objWorksheet.Cells(i+1, 2) = C_Name
Next
objWorkbook.Save
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
Comparison Examples:
i) One to One Binary comparison
ii) One to One Textual comparison
iii) Many to many comparison
‘One to One Comparison
‘Working with multiple excel files
Dim objExcel, objWorkbook1, objWorkbook2, objWorksheet1, objWorksheet2
Dim RowsCount, i, Expected, Actual
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook1 = objExcel.Workbooks.Open(“C:\Users\G C Reddy\Desktop\abc.xlsx”)
Set objWorkbook2 = objExcel.Workbooks.Open(“C:\Users\G C Reddy\Desktop\xyz.xlsx”)
Set objWorksheet1 = objWorkbook1.Worksheets(1)
Set objWorksheet2 = objWorkbook2.Worksheets(1)
RowsCount = objWorksheet1.UsedRange.Rows.Count
For i = 2 To RowsCount Step 1
Expected = objWorksheet1.Cells(i, 1)
Actual = objWorksheet2.Cells(i, 1)
If Expected = Actual Then
objWorksheet2.Cells(i, 2)= “Passed”
Else
objWorksheet2.Cells(i, 2)= “Failed”
End If
Next
objWorkbook2.Save
objExcel.Quit
Set objWorksheet2= Nothing
Set objWorksheet1= Nothing
Set objWorkbook2= Nothing
Set objWorkbook1= Nothing
Set objExcel= Nothing
‘One to One Textual Comparison
‘Working with multiple excel files
Dim objExcel, objWorkbook1, objWorkbook2, objWorksheet1, objWorksheet2
Dim RowsCount, i, Expected, Actual
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook1 = objExcel.Workbooks.Open(“C:\Users\G C Reddy\Desktop\abc.xlsx”)
Set objWorkbook2 = objExcel.Workbooks.Open(“C:\Users\G C Reddy\Desktop\xyz.xlsx”)
Set objWorksheet1 = objWorkbook1.Worksheets(1)
Set objWorksheet2 = objWorkbook2.Worksheets(1)
RowsCount = objWorksheet1.UsedRange.Rows.Count
For i = 2 To RowsCount Step 1
Expected = objWorksheet1.Cells(i, 1)
Actual = objWorksheet2.Cells(i, 1)
If StrComp(Expected, Actual, 1) = 0 Then
objWorksheet2.Cells(i, 2)= “Passed”
Else
objWorksheet2.Cells(i, 2)= “Failed”
End If
Next
objWorkbook2.Save
objExcel.Quit
Set objWorksheet2= Nothing
Set objWorksheet1= Nothing
Set objWorkbook2= Nothing
Set objWorkbook1= Nothing
Set objExcel= Nothing
‘Many to many Textual Comparison
‘Working with multiple excel files
Dim objExcel, objWorkbook1, objWorkbook2, objWorksheet1, objWorksheet2
Dim RowsCount, i, Expected, Actual
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook1 = objExcel.Workbooks.Open(“C:\Users\G C Reddy\Desktop\abc.xlsx”)
Set objWorkbook2 = objExcel.Workbooks.Open(“C:\Users\G C Reddy\Desktop\xyz.xlsx”)
Set objWorksheet1 = objWorkbook1.Worksheets(1)
Set objWorksheet2 = objWorkbook2.Worksheets(1)
RowsCount = objWorksheet1.UsedRange.Rows.Count
Flag = 0
For i = 2 To RowsCount Step 1
Expected = objWorksheet1.Cells(i, 1)
For j = 2 To RowsCount Step 1
Actual = objWorksheet2.Cells(j, 1)
If StrComp(Expected, Actual, 1) = 0 Then
Flag = 1
Exit for
Else
Flag = 0
End If
Next
If Flag = 1 Then
objWorksheet2.Cells(i, 2)= “Passed”
Else
objWorksheet2.Cells(i, 2)= “Failed”
End If
Next
objWorkbook2.Save
objExcel.Quit
Set objWorksheet2= Nothing
Set objWorksheet1= Nothing
Set objWorkbook2= Nothing
Set objWorkbook1= Nothing
Set objExcel= Nothing
‘Create an Excel file and Add one more sheet
Dim objExcel
Set objExcel = CreateObject(“Excel.Application”)
objexcel.Visible = True ‘To view the operation while execution
objExcel.Workbooks.Add ‘Create New file
objExcel.Worksheets.Add ‘Create New sheet
objExcel.ActiveWorkbook.SaveAs “C:\Users\G C Reddy\Desktop\UFT.xlsx”
objExcel.Quit ‘To close Excel Application
Set objExcel = Nothing ‘To release the memory.
————————————————–
Assignment:
————-
Create an excel file and move 1st sheet to 3rd position.
‘Create an Excel file and rename 1st sheet as “Module”, 2nd sheet as “TestCase” and 3rd sheet as “TestStep”.
Dim objExcel
Set objExcel = CreateObject(“Excel.Application”)
objexcel.Visible = True
objExcel.Workbooks.Add
objExcel.Worksheets(1).Name = “Module”
objExcel.Worksheets(2).Name = “TestCase”
objExcel.Worksheets(3).Name = “TestStep”
objExcel.ActiveWorkbook.SaveAs “C:\Users\G C Reddy\Desktop\UFT2.xlsx”
objExcel.Quit
Set objExcel = Nothing
Batch Testing / Batch Execution
Executing series of Tests is called batch testing
There are 4 ways for batch Testing in UFT:
i) Using “Test Batch Runner” tool
ii) Using AOM (Automation Object Model) script
iii) Using Driver script in automation framework
iv) Using ALM/QC tool
Steps for Batch Testing Using “Test Batch Runner” tool:
i) Create basic tests and run them once
ii) Launch “Test Batch Runner” and create Test batches.
iii) provide permission to “Test Batch Runner” to execute tests.
iv) Run test batches
——————————