QTP Excel Scripting

(1)

www.gcreddy.com

Visit:

www.gcreddy.com

for QTP Information

Excel Scripting in QTP

Excel File / Work Book Operations

---'Objects in Excel Object Model

a) Excel Application - Excel Application Object b) Excel Workbook / File - Workbook Object

c) Excel Worksheet / sheet - Worksheet

---Note: Without creating Work Book Object and Work Sheet Object, we can perform all Excel Application Operations using Excel

Application(Main) Object, but for user friendliness we use those objects.

'Creating Excel Application Object

Set Variable=CreateObject("Excel.Application")'Create Excel Application Object

Dim objExcel

Set objExcel=CreateObject("Excel.Application")

Important Operations on Excel files for Test Automation Using QuickTest Professional (QTP)

a) Create Excel Files b) Open Excel Files

(2)

www.gcreddy.com

c) Copy Excel Files d) Delete Excel Files e) Move Excel Files f) Read Data

e) Read Data for Data driven Testing f) Write Data

g) Write Test Result

h) Comparing data (One to one) i) Comparing data (One to Many) j) Comparing data (Many to one)

k) Comparing data (Many to Many Exact) l) Comparing data (Many to Many Textual) m) Searching for strings

Examples:

---1) 'Create Excel file /Work book

Dim objExcel

Set objExcel=CreateObject("Excel.Application") objExcel.Visible=True 'To view the Operations

objExcel.Workbooks.Add 'Creatining Excel file / workbook objExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\Administrator\Desktop\gcreddy.xls"

objExcel.Quit 'To Quit the Excel Application Set objExcel=Nothing

(3)

www.gcreddy.com

2) 'Check the existence of the File If exists then open the file and enter some data

' If Not exists Create the Excel file /Work book and enter some data Dim objExcel, objFso, FilePath

FilePath="C:\Documents and Settings\Administrator\Desktop\gcreddy.xls" Set objFso=CreateObject("Scripting.FileSystemObject") Set objExcel=CreateObject("Excel.Application") If objFso.FileExists(FilePath) Then objExcel.Workbooks.Open (FilePath) objExcel.Worksheets("Sheet1").Cells(1,1)="VB Script" objExcel.ActiveWorkbook.Save Else objExcel.Workbooks.Add objExcel.ActiveSheet.Cells(2,2)="VB Script" objExcel.ActiveWorkbook.SaveAs (Filepath) End If

objExcel.Quit 'To Quit the Excel Appliction Set objExcel=Nothing

---3) 'Fetch Test Data directly from an Excel file and perform Data driven testing for Login Operation

Dim objExcel, objWorkbook, objWorksheet

'Create Excel application Object that can be used to perform operations on Excel Appliction

'Create WorkBook Object using Excel application Object that can be used to perform operations on Excel Work Books

Set objWorkbook=objExcel.Workbooks.Open ("C:\Documents and Settings\Administrator\Desktop\input.xls")

'Create Work sheet object Using Work Book Object, that can be used to perform operations on Excel Sheets

Set objWorksheet=objWorkbook.Worksheets("Sheet1") Rows_Count=objWorksheet.usedrange.rows.count For i= 2 to Rows_Count Step 1

SystemUtil.Run "C:\Program Files\HP\QuickTest

Professional\samples\flight\app\flight4a.exe","","C:\Program Files\HP\QuickTest Professional\samples\flight\app\","open"

(4)

www.gcreddy.com

Dialog("Login").Activate

Dialog("Login").WinEdit("Agent Name:").Set objWorksheet.Cells(i,"A") Dialog("Login").WinEdit("Password:").Set objWorksheet.Cells(i,"B") Wait 1 Dialog("Login").WinButton("OK").Click Window("Flight Reservation").Close Next objExcel.Quit Set objWorksheet=Nothing Set objWorkbook=Nothing Set objExcel=Nothing

---4) 'Fetch Test Data directly from an Excel file and perform Data driven testing for Login Operation

'Export Test Results to the same file

Dim objExcel, objWorkbook, objWorksheet

'Create Excel application Object that can be used to perform operations on Excel Appliction

'Create WorkBook Object using Excel application Object that can be used to perform operations on Excel Work Books

'Create Work sheet object Using Work Book Object , that can be used to perform operations on Excel Sheets

Set objWorksheet=objWorkbook.Worksheets("Sheet1") objWorksheet.Cells(1,3)="Results"

Rows_Count=objWorksheet.usedrange.rows.count For i= 2 to Rows_Count Step 1

Professional\samples\flight\app\flight4a.exe","","C:\Program Files\HP\QuickTest Professional\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,"B") Wait 1

Dialog("Login").WinButton("OK").Click

If Window("Flight Reservation").Exist(12) Then Window("Flight Reservation").Close

objWorksheet.Cells(i,"C")="Login Successful"

(5)

www.gcreddy.com

Else SystemUtil.CloseDescendentProcesses objWorksheet.Cells(i,"C")="Login Filed" End If Next objWorkbook.Save objExcel.Quit Set objWorksheet=Nothing Set objWorkbook=Nothing Set objExcel=Nothing

5) 'Fetch Test Data directly from an Excel file and perform Data driven testing for Login Operation

'Export Test Results & Error Messgae to the same file Dim objExcel, objWorkbook, objWorksheet, rows_Count Set objExcel=CreateObject("Excel.Application")

Set objWorksheet=objWorkbook.Worksheets(1) objWorksheet.Cells(1,3)="Test Result"

objWorksheet.Cells(1,4)="Error Message"

rows_Count=objWorksheet.usedrange.rows.count For i= 2 to rows_Count Step 1

Dialog("Login").WinEdit("Agent Name:").Set objWorksheet.Cells(i, 1)

Dialog("Login").WinEdit("Password:").Set objWorksheet.Cells(i, "B") 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, 3)="Login Failed"

(6)

www.gcreddy.com

objWorksheet.Cells(i, 4)=Dialog("Login").Dialog("Flight

Reservations").Static("Agent name must be at").GetROProperty ("text") SystemUtil.CloseDescendentProcesses End If Next objWorkbook.Save objExcel.Quit Set objWorksheet=Nothing Set objWorkbook=Nothing Set objExcel=Nothing 6)

Using While...Wend Loop

---Dim objExcel, objWorkbook, objWorksheet, rows_Count, i Set objExcel=CreateObject("Excel.Application")

Set objWorkbook=objExcel.Workbooks.Open ("C:\Documents and Settings\Administrator\Desktop\input.xls") Set objWorksheet=objWorkbook.Worksheets(1) objWorksheet.Cells(1,3)="Test Result" objWorksheet.Cells(1,4)="Error Message" rows_Count=objWorksheet.usedrange.rows.count i= 2

While i<= rows_Count

Dialog("Login").WinEdit("Agent Name:").Set objWorksheet.Cells(i, 1)

Dialog("Login").WinEdit("Password:").Set objWorksheet.Cells(i, "B") 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, 3)="Login Failed"

objWorksheet.Cells(i, 4)=Dialog("Login").Dialog("Flight

Reservations").Static("Agent name must be at").GetROProperty ("text") SystemUtil.CloseDescendentProcesses

(7)

www.gcreddy.com

End If i=i+1 Wend objWorkbook.Save objExcel.Quit Set objWorksheet=Nothing Set objWorkbook=Nothing Set objExcel=Nothing

7) 'Capture Link names from Google home page and export to Excel file 3rd sheet

Dim ObjExcel,ObjWorkbook,ObjWorksheet Dim oLink,Links,myLink,i

Set ObjExcel=CreateObject("Excel.Application")

Set ObjWorkbook=ObjExcel.Workbooks.Open("C:\Documents and Settings\Administrator\Desktop\input.xls") Set ObjWorksheet=ObjWorkbook.Worksheets(3) ObjWorksheet.Cells(1,1)="Link Names" Set oLink=Description.Create oLink("micclass").value="Link" Set Links=Browser("title:=Google").Page("title:=Google").ChildObjects(oLink )

For i=0 to Links.Count-1 step 1

myLink=Links(i).GetRoProperty("text") ObjWorksheet.Cells(i+2,1)=myLink Next ObjWorkbook.Save ObjExcel.Quit Set ObjWorksheet=Nothing Set ObjWorkbook=Nothing

www.gcreddy.com

7

(8)

www.gcreddy.com

Set ObjExcel=Nothing

---8) 'Capture Button names from Login Dialog (Flight Reservation Application) and export to Excel file 3rd sheet

Dim ObjExcel,ObjWorkbook,ObjWorksheet Dim oButton,Buttons,myButton,i

Set ObjWorkbook=ObjExcel.Workbooks.Open("C:\Documents and Settings\Administrator\Desktop\input.xls") Set ObjWorksheet=ObjWorkbook.Worksheets(2) ObjWorksheet.Cells(1,1)="Button Names" 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

myButton=Buttons(i).GetRoProperty("text") ObjWorksheet.Cells(i+2,1)=myButton Next ObjWorkbook.Save ObjExcel.Quit Set ObjWorksheet=Nothing Set ObjWorkbook=Nothing Set ObjExcel=Nothing

-9) ' Read/capture order numbers and customer names from 1 - 10 orders in Flight Reservation window

' and export to excel file 2nd sheet

Dim objExcel, objWorkBook, objWorkSheet, ord, C_Name Set objExcel = createobject("Excel.Application")

Set objWorkBook = objExcel.Workbooks.Open("C:\Documents and Settings\gcr\Desktop\Sample.xls")

Set objWorkSheet = objWorkBook.Worksheets(2) objWorkSheet.cells(1,1) = "Order No."

objWorkSheet.cells(1,2) = "C-Name"

(9)

www.gcreddy.com

For ord= 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 ord Window("Flight Reservation").Dialog("Open Order").WinButton("OK").Click Wait 1 C_Name = Window("Flight Reservation").WinEdit("Name:").GetROProperty("text") objWorkSheet.cells(ord+1,1) = ord objWorkSheet.cells(ord+1,2) =C_Name Next objWorkBook.Save objExcel.Quit Set objWorkSheet=Nothing Set objWorkBook=Nothing Set objExcel=Nothing

10) One to One Comparison and Exact match

---'Capture Button names from Login Dialog (Flight Reservation Application) and Perform One to One Comparison and Exact match Dim ObjExcel,ObjWorkbook,ObjWorksheet

Dim oButton,Buttons,myButton,i

Set ObjWorkbook=ObjExcel.Workbooks.Open("C:\Documents and Settings\Administrator\Desktop\input.xls") Set ObjWorksheet=ObjWorkbook.Worksheets(2) ObjWorksheet.Cells(1,2)="Buttons" 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

myButton=Buttons(i).GetRoProperty("text") ObjWorksheet.Cells(i+2, 2)=myButton

(10)

www.gcreddy.com

Expected=ObjWorksheet.Cells(j, 1) Actual=ObjWorksheet.Cells(j, 2) If Expected=Actual Then ObjWorksheet.Cells(j, 3)="Pass" Else ObjWorksheet.Cells(j, 3)="Fail" End If Next ObjWorkbook.Save ObjExcel.Quit Set ObjWorksheet=Nothing Set ObjWorkbook=Nothing Set ObjExcel=Nothing

11) One to One Textual Comparison

---'Capture Button names from Login Dialog (Flight Reservation Application) and Perform and Perform One to One Textual Comparison Dim ObjExcel,ObjWorkbook,ObjWorksheet

myButton=Buttons(i).GetRoProperty("text") ObjWorksheet.Cells(i+2, 2)=myButton Next

(11)

www.gcreddy.com

Expected=ObjWorksheet.Cells(j, 1) Actual=ObjWorksheet.Cells(j, 2)

If StrComp (Expected,Actual,1)=0 Then ObjWorksheet.Cells(j, 3)="Pass" Else ObjWorksheet.Cells(j, 3)="Fail" End If Next ObjWorkbook.Save ObjExcel.Quit Set ObjWorksheet=Nothing Set ObjWorkbook=Nothing Set ObjExcel=Nothing

---12) Many to Many Comparison

---'Capture Button names from Login Dialog (Flight Reservation Application) and Perform and Perform Many to Many Comparison Dim ObjExcel,ObjWorkbook,ObjWorksheet

Expected=ObjWorksheet.Cells(j, 1)

(12)

www.gcreddy.com

For k=2 to rows_Count step 1

Actual=ObjWorksheet.Cells(k, 2) If Expected=Actual Then Flag =1 Exit for else Flag= 0 End If next If Flag=1 Then ObjWorksheet.Cells(j, 3)="Pass" Else ObjWorksheet.Cells(j, 3)="Fail" End If Next ObjWorkbook.Save ObjExcel.Quit Set ObjWorksheet=Nothing Set ObjWorkbook=Nothing Set ObjExcel=Nothing

---13) Many to Many Textual Comparison

'Capture Button names from Login Dialog (Flight Reservation Application) and Perform and Perform Many to Many Textual Comparison

---'Capture Button names from Google home page and export to Excel file 3rd sheet

Set ObjWorkbook=ObjExcel.Workbooks.Open("C:\Documents and Settings\Administrator\Desktop\input.xls")

Set ObjWorksheet=ObjWorkbook.Worksheets(2) ObjWorksheet.Cells(1,2)="Buttons"

(13)

www.gcreddy.com

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(j, 1) For k=2 to rows_Count step 1

Actual=ObjWorksheet.Cells(k, 2) If StrComp (Expected,Actual,1)= 0 Then

Flag =1 Exit for else Flag= 0 End If next If Flag=1 Then ObjWorksheet.Cells(j, 3)="Pass" Else ObjWorksheet.Cells(j, 3)="Fail" End If Next ObjWorkbook.Save ObjExcel.Quit Set ObjWorksheet=Nothing Set ObjWorkbook=Nothing Set ObjExcel=Nothing --

---14) 'Create Excel file and Rename 1st sheet as "Module", 2nd Sheet as "Test Case", 'and 3rd Sheet as "Test Step"

Dim objExcel

(14)

www.gcreddy.com

objExcel.Visible=True objExcel.Workbooks.Add objExcel.Worksheets("Sheet1").Name="Module" Wait 4 objExcel.Worksheets("Sheet2").Name="TestCase" Wait 4 objExcel.Worksheets("Sheet3").Name="TestStep" objExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\Administrator\Desktop\abcd.xls"

objExcel.Quit

Set objExcel=Nothing

---15) 'Create an Excel file and add one more

Dim objExcel

Set objExcel=CreateObject("Excel.Application") objExcel.Visible=True

objExcel.Workbooks.Add 'Creating Work Book objExcel.Worksheets.Add 'Creating Work Sheet Wait 4

objExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\Administrator\Desktop\abcde.xls"

objExcel.Quit

Set objExcel=Nothing

--

----15) 'Capture Button names from Login Dialog (Flight Reservation Application) and perform Many to Many Complete Comparison

Capture Button names from Google home page and export to Excel file 3rd sheet

Set ObjWorkbook=ObjExcel.Workbooks.Open("C:\Documents and Settings\Administrator\Desktop\input.xls")

Set ObjWorksheet=ObjWorkbook.Worksheets(2) ObjWorksheet.Cells(1,2)="Buttons"

(15)

www.gcreddy.com

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

rows_Count= ObjWorksheet.usedrange.rows.count x =0

For j= 2 to rows_Count step 1

Expected=ObjWorksheet.Cells(j, 1) flag = 0

For k=2 to rows_Count step 1

Actual=ObjWorksheet.Cells(k, 2)

If StrComp (Expected,Actual,1)= 0 Then Flag =1

End If

x=x+1 ' increment the comparison count next If Flag=1 Then ObjWorksheet.Cells(j, 3)="Pass" Else ObjWorksheet.Cells(j, 3)="Fail" End If

msgbox x 'inner loop comparison values Next

msgbox x ' Total number of comparisons ObjWorkbook.Save ObjExcel.Quit Set ObjWorksheet=Nothing Set ObjWorkbook=Nothing Set ObjExcel=Nothing

www.gcreddy.com

15