VBScript Excel Object Model Part-2
1) Read Test information from Excel file as well as perform Data driven testing for Login Functionality. And write/export Test Result as well as 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 = ii 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 2d 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 elevate must hold upward 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
objWorksheet.Cells(1, 3) = "Test Result"
objWorksheet.Cells(1, 4) = "Error Message"
For i = ii 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 2d 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 elevate must hold upward 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 abode page as well as export to excel file 2d 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 inward FR window as well as Read OrderNo as well as Customer names, export to Excel file third 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 as well as Rename 1st canvas every bit "Module, 2d canvas every bit "TestCase" as well as third canvas every bit "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 as well as add together ane to a greater extent than 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 as well as Move 1st canvas to third 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 information from Excel as well as compare (One to One, Binary Comparison) ii 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 = ii 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 information from Excel as well as compare(One to One, Textual Comparison) ii 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 = ii 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 information from Excel as well as compare(Many to Many, Textual Comparison) ii 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
Flag = 0
For i = ii To Rows_Count Step 1
Expected = objWorksheet.cells(i, 1)
For j = ii 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 as well as compare alongside expected (expected inward Excel file)
ii) Compare ii columns(with dissimilar rows size) data.
------------------------------------------------------------
1) Read Test information from Excel file as well as perform Data driven testing for Login Functionality. And write/export Test Result as well as 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 = ii 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 2d 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 elevate must hold upward 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
objWorksheet.Cells(1, 3) = "Test Result"
objWorksheet.Cells(1, 4) = "Error Message"
For i = ii 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 2d 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 elevate must hold upward 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 abode page as well as export to excel file 2d 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 inward FR window as well as Read OrderNo as well as Customer names, export to Excel file third 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 as well as Rename 1st canvas every bit "Module, 2d canvas every bit "TestCase" as well as third canvas every bit "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 as well as add together ane to a greater extent than 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 as well as Move 1st canvas to third 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 information from Excel as well as compare (One to One, Binary Comparison) ii 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 = ii 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 information from Excel as well as compare(One to One, Textual Comparison) ii 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 = ii 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 information from Excel as well as compare(Many to Many, Textual Comparison) ii 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
Flag = 0
For i = ii To Rows_Count Step 1
Expected = objWorksheet.cells(i, 1)
For j = ii 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 as well as compare alongside expected (expected inward Excel file)
ii) Compare ii columns(with dissimilar rows size) data.
------------------------------------------------------------