Excel Scripting in UFT

Excel Scripting in UFT
Excel Scripts Video

VBScript Excel Application Object

It is used to perform operations on Excel Application or Excel work books.

Create Excel Application Object

Set Variable = CreateObject (“Excel.Application”)

Three entities of Microsoft Excel
a) Excel Application

b) Excel File / Workbook

c) Excel Sheet / Work sheet
--------------------------------------
VBScript Excel Scripting Examples for UFT:

1) Create Excel File
 

Dim objExcel
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True 'To view the operation during execution

objExcel.Workbooks.Add ' Create Excel file
objExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\gcreddy\Desktop\abcd.xls"

objExcel.Quit 'To close Excel Application

Set objExcel= Nothing
--------------------------------------
2) Check the existence of Excel file, If not exists then Create the File
Dim objFso, objExcel
Set objFso =CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")

If Not objFso.FileExists("C:\Documents and Settings\gcreddy\Desktop\abcd.xls") Then
objExcel.Workbooks.Add
objExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\gcreddy\Desktop\abcd.xls"
End If

objExcel.Quit

Set objExcel= Nothing
-------------------------------------------
3) Check the existence of Excel file, If exists then open the File and write some data
If not exists create the file and write some data

Dim objFso, objExcel, FilePath
FilePath = "C:\Documents and Settings\gcreddy\Desktop\abcd.xls"
Set objFso =CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")

If objFso.FileExists(FilePath) Then
objExcel.Workbooks.Open(FilePath)
objExcel.Worksheets(1).Cells(1, 1) ="VBScript"
objExcel.ActiveWorkbook.Save
Else
objExcel.Workbooks.Add
objExcel.Worksheets(1).Cells(1, 1) ="VBScript"
objExcel.ActiveWorkbook.SaveAS (FilePath)
End If

objExcel.Quit

Set objExcel= Nothing
Set objFso = Nothing
--------------------------------------
Excel Objects in VBScript for UFT:

Excel Application Object

Set Variable = CreateObject("Excel.Application")
----------------------------------

Excel WorkBook Object

Set Variable = ExcelAPplicationObject.Workbooks.Add / Open("FilePath")
--------------------------------------
Excel WorkSheet Object

Set Variable = ExcelWorkBookObject.Worksheets(SheetId)
--------------------------------------
4) Check the existence of Excel file, If exists then open the File and write some data, If not exists create the file and write some data
Dim objFso, objExcel, FilePath, objWorkbook, objWorksheet
FilePath = "C:\Documents and Settings\gcreddy\Desktop\abcd.xls"
Set objFso =CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")

If objFso.FileExists(FilePath) Then
Set objWorkbook = objExcel.Workbooks.Open(FilePath)
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(1, 1) ="VBScript"
objWorkbook.Save
Else
Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(1, 1) ="VBScript"
objWorkbook.SaveAs(FilePath)
End If

objExcel.Quit
Set objWorksheet=Nothing
Set objWorkbook = Nothing
Set objExcel= Nothing
Set objFso = Nothing
--------------------------------------
5) Read data from an Excel file and Data Driven Testing for Login Operation

Dim objExcel, objWorkbook, objWorksheet, RowCount
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and Settings\gcreddy\Desktop\abcd.xls")
Set objWorksheet =objWorkbook.Worksheets(1)

RowCount = objWorksheet.UsedRange.Rows.Count

For i = 2 To RowCount Step 1
SystemUtil.Run "C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe","","C:\Program Files\HP\Unified Functional Testing\samples\flight\app\","open"
Dialog("Login").Activate
Dialog("Login").WinEdit("Agent Name:").Set objWorksheet.Cells(i, "A") Dialog("Login").WinEdit("Password:").Set objWorksheet.Cells(i, 2wait 2
Dialog("Login").WinButton("OK").Click
Window("Flight Reservation").Close
Next

objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook= Nothing
Set objExcel = Nothing
--------------------------------------
6) Read data from an Excel file and Data Driven Testing for Login Operation
Export Results and Error messages to the same file


Dim objexcel, objworkbook, objworksheet, rowcount, i

Set objexcel = createobject("excel.application")
Set objworkbook = objexcel.Workbooks.Open("C:\Documents and Settings\gcreddy\Desktop\abcd.xls")
Set objworksheet = objworkbook.Worksheets(1)

rowcount = objworksheet.usedrange.rows.count

objworksheet.cells(1, 3) = "Test Result"
objworksheet.cells(1, 4) = "Error Message"

For i = 2 To Rowcount Step 1
SystemUtil.Run "C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe","","C:\Program Files\HP\Unified Functional Testing\samples\flight\app\","open"
Dialog("Login").Activate
Dialog("Login").WinEdit("Agent Name:").Set objworksheet.cells(i, 1) Dialog("Login").WinEdit("Password:").Set objworksheet.cells(i, 2)
Dialog("Login").WinButton("OK").Click

If window("Flight Reservation").Exist(12) Then
Window("Flight Reservation").Close
objworksheet.cells(i, 3) = "Login Successful"
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 not Successful"
End If
Next

objworkbook.Save
objexcel.Quit
Set objworksheet = Nothing
Set objworkbook = Nothing
Set objexcel = Nothing
--------------------------------------
7) Create Excel file and add one more sheet

Dim objExcel
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add 'Create Excel File
objExcel.Worksheets.Add 'Create Excel Sheet

objExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\gcreddy\Desktop\xyz.xls"

objExcel.Quit
Set objExcel = Nothing
--------------------------------------
8) Create Excel file and add rename 1st sheet as "Module", 2nd sheet as "TestCase" and 3rd sheet as Teststep

Dim objExcel
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add

objExcel.Worksheets(1).Name = "Module"
objExcel.Worksheets(2).Name = "TestCase"
objExcel.Worksheets(3).Name = "TestStep"

objExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\gcreddy\Desktop\Sample.xls"

objExcel.Quit
Set objExcel = Nothing
--------------------------------------
Comparison Examples:
----------------------
a) One to one (Binary)
b) One to one (Textual)

c) Many to many(Binary)
d) Many to many(Textual)
--------------------------------------
9) Read Button Names from Login Dialog and compare with expected in Excel
Dim objExcel, objWorkbook, objWorksheet, oButton, Buttons, i
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook =objExcel.Workbooks.Open("C:\Documents and Settings\gcreddy\Desktop\abcd.xls")
Set objWorksheet = objWorkbook.Worksheets(1)

Set oButton = Description.Create
oButton("Class Name").Value = "WinButton"

Set Buttons = Dialog("text:=Login").ChildObjects(oButton)

For i = 0 To Buttons.Count -1 Step 1
If objWorksheet.Cells(i+2, 1) = Buttons(i).GetRoProperty("text") Then
    objWorksheet.Cells(i+2, 3) = "Passed"
    Else
    objWorksheet.Cells(i+2, 3) = "Failed"
End If
   
Next
objWorkbook.Save
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
--------------------------------------
10) Read Button Names from Login Dialog and compare with expected in Excel
Dim objExcel, objWorkbook, objWorksheet, oButton, Buttons, i
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook =objExcel.Workbooks.Open("C:\Documents and Settings\gcreddy\Desktop\abcd.xls")
Set objWorksheet = objWorkbook.Worksheets(1)

Set oButton = Description.Create
oButton("Class Name").Value = "WinButton"

Set Buttons = Dialog("text:=Login").ChildObjects(oButton)

For i = 0 To Buttons.Count -1 Step 1
Expected = objWorksheet.Cells(i+2, 1)
objWorksheet.Cells(i+2, 2) = Buttons(i).GetRoProperty("text")
Actual = objWorksheet.Cells(i+2, 2)
If Expected = Actual Then
    objWorksheet.Cells(i+2, 3) ="Passed"
    Else
    objWorksheet.Cells(i+2, 3) ="Failed"
End If
   
Next
objWorkbook.Save
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
--------------------------------------
11) Read Button Names from Login Dialog and compare (One to One, Textual) with expected in Excel

Dim objExcel, objWorkbook, objWorksheet, oButton, Buttons, i
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook =objExcel.Workbooks.Open("C:\Documents and Settings\gcreddy\Desktop\abcd.xls")
Set objWorksheet = objWorkbook.Worksheets(1)

Set oButton = Description.Create
oButton("Class Name").Value = "WinButton"

Set Buttons = Dialog("text:=Login").ChildObjects(oButton)

For i = 0 To Buttons.Count -1 Step 1
Expected = objWorksheet.Cells(i+2, 1)
objWorksheet.Cells(i+2, 2) = Buttons(i).GetRoProperty("text")
Actual = objWorksheet.Cells(i+2, 2)
If StrComp (Expected, Actual, 1) = 0 Then
    objWorksheet.Cells(i+2, 3) ="Passed"
    Else
    objWorksheet.Cells(i+2, 3) ="Failed"
End If
   
Next
objWorkbook.Save
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
--------------------------------------
12) Read Button Names from Login Dialog and compare (Many to Many, Textual) with expected in Excel

Dim objExcel, objWorkbook, objWorksheet, oButton, Buttons, i
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook =objExcel.Workbooks.Open("C:\Documents and Settings\gcreddy\Desktop\abcd.xls")
Set objWorksheet = objWorkbook.Worksheets(1)

Set oButton = Description.Create
oButton("Class Name").Value = "WinButton"

Set Buttons = Dialog("text:=Login").ChildObjects(oButton)


For i = 0 To Buttons.Count -1 Step 1
objWorksheet.Cells(i+2, 2) = Buttons(i).GetRoProperty("text")
Next

RowCount = objWorksheet.UsedRange.Rows.count
Flag = 0
For j = 2 To RowCount Step 1
Expected = objWorksheet.Cells(j, 1)   

For k = 2 To RowCount Step 1
Actual = objWorksheet.Cells(k, 2)       

If StrComp (Expected, Actual, 1) = 0 Then
    Flag = 1
    Exit For
End If
Next
If  Flag = 1 Then
    objWorksheet.Cells(j, 3) ="Passed"
    Else
    objWorksheet.Cells(j, 3) ="Failed"
End If
   
Next
objWorkbook.Save
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
--------------------------------------
Working with multiple Excel Files:

13) Read Expected and Actual values from  first file, perform Many to Many Textual comparison and Export test result to second file.

 
Dim objExcel, objWorkbook1, objworkbook2, objworksheet1, objworksheet2
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook1 = objExcel.Workbooks.Open("C:\Documents and Settings\gcreddy\Desktop\abcd.xls")
Set objWorkbook2 = objExcel.Workbooks.Open("C:\Documents and Settings\gcreddy\Desktop\xyz.xls")
Set objworksheet1 = objworkbook1.Worksheets(1)
Set objworksheet2 = objworkbook2.Worksheets(1)

RowCount = objworksheet1.usedrange.rows.count
Flag = 0
For i = 2 To RowCount Step 1
Expected = objworksheet1.cells(i,1)   

For j = 2 To RowCount Step 1
Actual = objworksheet1.cells(j, 2)   
If StrComp (Expected,Actual, 1) = 0 Then
    Flag = 1
    Exit For
End If
Next
If Flag = 1 Then
objworksheet2.cells(i, 1) = "Passed"   
Else
objworksheet2.cells(i, 1) = "Failed"
End If

Next

objworkbook2.Save
objExcel.Quit

Set objworksheet1=Nothing
Set objworksheet2=Nothing
Set objworkbook1=Nothing
Set objworkbook2=Nothing
Set objExcel=Nothing

0 comments:

Post a Comment