VBScript Excel Object Model Part-2

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

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")
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

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 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

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 = 2 To Rows_Count Step 1
    Expected = objWorksheet.cells(i, 1)
   
    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.
------------------------------------------------------------

0 comments:

Post a Comment