VBScript Excel Object Model Part-2

1) Read Test data from Excel file and perform Data driven testing for Login Functionality. And write/export Test Result and Error Message to the same file.

Dim objExcel, objWorkbook, objWorksheet, RowsCount, i

Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Users\G C REDDY\Desktop\January.xlsx”)
Set objWorksheet = objWorkbook.Worksheets(1)

RowsCount = objWorksheet.UsedRange.Rows.Count

objWorksheet.Cells(1, 3) = “Test Result”
objWorksheet.Cells(1, 4) = “Error Message”
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) ‘i for Row, 1 for 1st column
Dialog(“Login”).WinEdit(“Password:”).Set objWorksheet.Cells(i, “B”) ‘i for Row, B for 2nd Column
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(“Agent name must be at”).GetROProperty(“text”)
SystemUtil.CloseDescendentProcesses
objWorksheet.Cells(i, 3) = “Login Failed -Failed”
End If
Next
objWorkbook.Save
objExcel.Quit

Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

Dim objExcel, objWorkbook, objWorksheet, RowsCount, i

Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Users\G C REDDY\Desktop\January.xlsx”)
Set objWorksheet = objWorkbook.Worksheets(1)

RowsCount = objWorksheet.UsedRange.Rows.Count

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(“Agent name must be at”).GetROProperty(“text”)
objWorksheet.Cells(i, 3) = “Login Failed -Failed”

If Dialog(“Login”).Dialog(“Flight Reservations”).Exist(4) Then
Dialog(“Login”).Dialog(“Flight Reservations”).WinButton(“OK”).Click
Dialog(“Login”).WinButton(“Cancel”).Click
End If
End If
Next
objWorkbook.Save
objExcel.Quit

Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

3) Read Link Names from Google home page and export to excel file 2nd sheet.

Dim objExcel, objWorkbook, objWorksheet, oLink, Links

Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Users\G C REDDY\Desktop\January.xlsx”)
Set objWorksheet = objWorkbook.Worksheets(2)

Set oLink = Description.Create
oLink(“micclass”).Value = “Link”

SystemUtil.Run “C:\Program Files\Internet Explorer\iexplore.exe”, “www.google.com”
Set Links = Browser(“CreationTime:=0”).Page(“title:=Google”).ChildObjects(oLink)
objWorksheet.Cells(1, 1) = “Link Names”
For i = 0 To Links.Count-1 Step 1
objWorksheet.Cells(i+2, 1) = Links(i).GetRoProperty(“text”)
Next
objWorkbook.Save
objExcel.Quit

Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

4) Open 1 to 10 Orders in FR window and Read OrderNo and Customer names, export to Excel file 3rd sheet.

Dim objExcel, objWorkbook, objWorksheet

Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Users\G C REDDY\Desktop\January.xlsx”)
Set objWorksheet = objWorkbook.Worksheets(3)

objWorksheet.cells(1, 1) =”OrderNo”
objWorksheet.cells(1, 2) = “Customer 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
objWorksheet.cells(i+1, 1) = Window(“Flight Reservation”).WinEdit(“Order No:”).GetROProperty(“text”)
objWorksheet.cells(i+1, 2) =Window(“Flight Reservation”).WinEdit(“Name:”).GetROProperty(“text”)

Next
objWorkbook.Save
objExcel.Quit

Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

5) 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\QTP.xlsx”
objExcel.Quit
Set objExcel = Nothing

6) Create an Excel file and add one more sheet.

Dim objExcel
Set objExcel = CreateObject(“excel.Application”)
objExcel.Visible = True

objExcel.Workbooks.Add ‘To Create New File/Workbook
objExcel.Worksheets.Add ‘To Create New sheet

objExcel.ActiveWorkbook.SaveAs “C:\Users\G C REDDY\Desktop\QTP2.xlsx”
objExcel.Quit
Set objExcel = Nothing

Assignment

Create an Excel file and Move 1st sheet to 3rd position.

Data Comparison

One to One Binary Comparison
One to One Textual Comparison

Many to Many (Binary Comparison)
Many to Many (Textual Comparison)

7) Read data from Excel and compare (One to One, Binary Comparison) 2 columns.

Dim objExcel, objWorkbook, objWorksheet, Rows_Count, i, Expected, Actual

Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Users\G C REDDY\Desktop\QTP.xlsx”)
Set objWorksheet = objWorkbook.Worksheets(1)
Rows_Count = objWorksheet.UsedRange.Rows.Count

For i = 2 To Rows_Count Step 1
Expected = objWorksheet.cells(i, 1)
Actual = objWorksheet.cells(i, 2)

If Expected = Actual Then
objWorksheet.cells(i, 3) = “Passed”
Else
objWorksheet.cells(i, 3) = “Failed”
End If
Next

objWorkbook.Save
objExcel.Quit

Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

8) Read data from Excel and compare(One to One, Textual Comparison) 2 columns.

Dim objExcel, objWorkbook, objWorksheet, Rows_Count, i, Expected, Actual

For i = 2 To Rows_Count Step 1
Expected = objWorksheet.cells(i, 1)
Actual = objWorksheet.cells(i, 2)

If StrComp (Expected, Actual, 1) = 0 Then
objWorksheet.cells(i, 3) = “Passed”
Else
objWorksheet.cells(i, 3) = “Failed”
End If
Next

objWorkbook.Save
objExcel.Quit

Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

9) Read data from Excel and compare(Many to Many, Textual Comparison) 2 columns.

Dim objExcel, objWorkbook, objWorksheet, Rows_Count, i, Expected, Actual

For j = 2 To Rows_Count Step 1
Actual = objWorksheet.cells(j, 2)

If StrComp(Expected, Actual, 1) = 0 Then
Flag = 1
Exit For
Else
Flag = 0
End If
Next
If Flag = 1 Then
obJWorksheet.cells(i, 3) =”Passed”
Else
obJWorksheet.cells(i, 3) =”Failed”
End If
Next

objWorkbook.Save
objExcel.Quit

Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

Assignments:
i) Read Button Names from Login Dialog box and compare with expected (expected in Excel file)

ii) Compare 2 columns(with different rows size) data.

Follow me on social media: