qtp excel scripting
DESCRIPTION
QTP Excel Scripting Examples; 'Objects in Excel Object Modela) Excel Application - Excel Application Objectb Excel Workbook / File - Workbook Objectc) Excel Worksheet / sheet - WorksheetImportant Operations on Excel files for Test Automation Using QuickTest Professional (QTP)a) Create Excel Filesb) Open Excel Filesc) Copy Excel Filesd) Delete Excel Filese) Move Excel Filesf) Read Datae) Read Data for Data driven Testingf) Write Datag) Write Test Resulth) 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 stringsTRANSCRIPT
www.gcreddy.com
Visit:
www.gcreddy.comfor 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 ObjectDim objExcelSet objExcel=CreateObject("Excel.Application")
Important Operations on Excel files for Test Automation Using QuickTest Professional (QTP)
a) Create Excel Files
b) Open Excel Files
www.gcreddy.com 1
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 bookDim objExcelSet objExcel=CreateObject("Excel.Application")objExcel.Visible=True 'To view the OperationsobjExcel.Workbooks.Add 'Creatining Excel file / workbookobjExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\Administrator\Desktop\gcreddy.xls"
objExcel.Quit 'To Quit the Excel ApplicationSet objExcel=Nothing
-----------------------------------------------------------
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
www.gcreddy.com 2
www.gcreddy.com
Dim objExcel, objFso, FilePathFilePath="C:\Documents and Settings\Administrator\Desktop\gcreddy.xls"Set objFso=CreateObject("Scripting.FileSystemObject")Set objExcel=CreateObject("Excel.Application")
If objFso.FileExists(FilePath) ThenobjExcel.Workbooks.Open (FilePath)objExcel.Worksheets("Sheet1").Cells(1,1)="VB Script"objExcel.ActiveWorkbook.SaveElseobjExcel.Workbooks.Add objExcel.ActiveSheet.Cells(2,2)="VB Script"objExcel.ActiveWorkbook.SaveAs (Filepath)
End If
objExcel.Quit 'To Quit the Excel ApplictionSet 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 ApplictionSet objExcel=CreateObject("Excel.Application")'Create WorkBook Object using Excel application Object that can be used to perform operations on Excel Work BooksSet 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 SheetsSet objWorksheet=objWorkbook.Worksheets("Sheet1")Rows_Count=objWorksheet.usedrange.rows.count
For i= 2 to Rows_Count Step 1SystemUtil.Run "C:\Program Files\HP\QuickTest Professional\samples\flight\app\flight4a.exe","","C:\Program Files\HP\QuickTest Professional\samples\flight\app\","open"Dialog("Login").ActivateDialog("Login").WinEdit("Agent Name:").Set objWorksheet.Cells(i,"A")Dialog("Login").WinEdit("Password:").Set objWorksheet.Cells(i,"B")Wait 1Dialog("Login").WinButton("OK").ClickWindow("Flight Reservation").Close
www.gcreddy.com 3
www.gcreddy.com
NextobjExcel.QuitSet objWorksheet=NothingSet objWorkbook=NothingSet 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 fileDim objExcel, objWorkbook, objWorksheet'Create Excel application Object that can be used to perform operations on Excel ApplictionSet objExcel=CreateObject("Excel.Application")'Create WorkBook Object using Excel application Object that can be used to perform operations on Excel Work BooksSet 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 SheetsSet objWorksheet=objWorkbook.Worksheets("Sheet1")objWorksheet.Cells(1,3)="Results"Rows_Count=objWorksheet.usedrange.rows.count
For i= 2 to Rows_Count Step 1SystemUtil.Run "C:\Program Files\HP\QuickTest Professional\samples\flight\app\flight4a.exe","","C:\Program Files\HP\QuickTest Professional\samples\flight\app\","open"Dialog("Login").ActivateDialog("Login").WinEdit("Agent Name:").Set objWorksheet.Cells(i,"A")Dialog("Login").WinEdit("Password:").Set objWorksheet.Cells(i,"B")Wait 1Dialog("Login").WinButton("OK").Click
If Window("Flight Reservation").Exist(12) ThenWindow("Flight Reservation").CloseobjWorksheet.Cells(i,"C")="Login Successful"Else
SystemUtil.CloseDescendentProcessesobjWorksheet.Cells(i,"C")="Login Filed"End If
NextobjWorkbook.SaveobjExcel.QuitSet objWorksheet=Nothing
www.gcreddy.com 4
www.gcreddy.com
Set objWorkbook=NothingSet 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 fileDim objExcel, objWorkbook, objWorksheet, rows_Count
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
For i= 2 to rows_Count Step 1SystemUtil.Run "C:\Program Files\HP\QuickTest Professional\
samples\flight\app\flight4a.exe","","C:\Program Files\HP\QuickTest Professional\samples\flight\app\","open"
Dialog("Login").ActivateDialog("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) ThenWindow("Flight Reservation").Close
objWorksheet.Cells(i, 3)="Login Successful"ElseobjWorksheet.Cells(i, 3)="Login Failed"objWorksheet.Cells(i, 4)=Dialog("Login").Dialog("Flight Reservations").Static("Agent name must be at").GetROProperty ("text")SystemUtil.CloseDescendentProcessesEnd IfNext
objWorkbook.SaveobjExcel.QuitSet objWorksheet=NothingSet objWorkbook=NothingSet objExcel=Nothing6)Using While...Wend Loop
www.gcreddy.com 5
www.gcreddy.com
------------------------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
SystemUtil.Run "C:\Program Files\HP\QuickTest Professional\samples\flight\app\flight4a.exe","","C:\Program Files\HP\QuickTest Professional\samples\flight\app\","open"
Dialog("Login").ActivateDialog("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) ThenWindow("Flight Reservation").Close
objWorksheet.Cells(i, 3)="Login Successful"ElseobjWorksheet.Cells(i, 3)="Login Failed"objWorksheet.Cells(i, 4)=Dialog("Login").Dialog("Flight Reservations").Static("Agent name must be at").GetROProperty ("text")SystemUtil.CloseDescendentProcessesEnd Ifi=i+1Wend
objWorkbook.SaveobjExcel.QuitSet objWorksheet=NothingSet objWorkbook=NothingSet objExcel=Nothing
7) 'Capture Link names from Google home page and export to Excel file 3rd sheet
Dim ObjExcel,ObjWorkbook,ObjWorksheetDim oLink,Links,myLink,i
www.gcreddy.com 6
www.gcreddy.com
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
Set ObjExcel=Nothing----------------------------------------------------------------------------------8) 'Capture Button names from Login Dialog (Flight Reservation Application) and export to Excel file 3rd sheet
Dim ObjExcel,ObjWorkbook,ObjWorksheetDim oButton,Buttons,myButton,i
Set ObjExcel=CreateObject("Excel.Application")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.CreateoButton("Class Name").value="WinButton"Set Buttons=Dialog("text:=Login").ChildObjects(oButton)
www.gcreddy.com 7
www.gcreddy.com
For i=0 to Buttons.Count-1 step 1myButton=Buttons(i).GetRoProperty("text")ObjWorksheet.Cells(i+2,1)=myButton
Next
ObjWorkbook.SaveObjExcel.QuitSet ObjWorksheet=NothingSet ObjWorkbook=NothingSet ObjExcel=Nothing-----------------------------------------------------------------------------------9) ' Read/capture order numbers and customer names from 1 - 10 orders in Flight Reservation window
' and export to excel file 2nd sheetDim objExcel, objWorkBook, objWorkSheet, ord, C_NameSet 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"
For ord= 1 to 10 Step 1Window("Flight Reservation").ActivateWindow("Flight Reservation").WinButton("Button").ClickWindow("Flight Reservation").Dialog("Open
Order").WinCheckBox("Order No.").Set "ON"Window("Flight Reservation").Dialog("Open
Order").WinEdit("Edit").Set ordWindow("Flight Reservation").Dialog("Open
Order").WinButton("OK").ClickWait 1C_Name = Window("Flight
Reservation").WinEdit("Name:").GetROProperty("text")objWorkSheet.cells(ord+1,1) = ordobjWorkSheet.cells(ord+1,2) =C_NameNext
objWorkBook.SaveobjExcel.QuitSet objWorkSheet=NothingSet objWorkBook=NothingSet objExcel=Nothing
www.gcreddy.com 8
www.gcreddy.com
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,ObjWorksheetDim oButton,Buttons,myButton,i
Set ObjExcel=CreateObject("Excel.Application")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.CreateoButton("Class Name").value="WinButton"Set Buttons=Dialog("text:=Login").ChildObjects(oButton)
For i=0 to Buttons.Count-1 step 1myButton=Buttons(i).GetRoProperty("text")ObjWorksheet.Cells(i+2, 2)=myButton
Nextrows_Count= ObjWorksheet.usedrange.rows.countFor j= 2 to rows_Count step 1Expected=ObjWorksheet.Cells(j, 1)Actual=ObjWorksheet.Cells(j, 2)
If Expected=Actual ThenObjWorksheet.Cells(j, 3)="Pass"ElseObjWorksheet.Cells(j, 3)="Fail"End IfNext
ObjWorkbook.SaveObjExcel.QuitSet ObjWorksheet=NothingSet ObjWorkbook=NothingSet 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
www.gcreddy.com 9
www.gcreddy.com
Dim ObjExcel,ObjWorkbook,ObjWorksheetDim oButton,Buttons,myButton,i
Set ObjExcel=CreateObject("Excel.Application")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.CreateoButton("Class Name").value="WinButton"Set Buttons=Dialog("text:=Login").ChildObjects(oButton)
For i=0 to Buttons.Count-1 step 1myButton=Buttons(i).GetRoProperty("text")ObjWorksheet.Cells(i+2, 2)=myButton
Nextrows_Count= ObjWorksheet.usedrange.rows.countFor j= 2 to rows_Count step 1Expected=ObjWorksheet.Cells(j, 1)Actual=ObjWorksheet.Cells(j, 2)
If StrComp (Expected,Actual,1)=0 ThenObjWorksheet.Cells(j, 3)="Pass"ElseObjWorksheet.Cells(j, 3)="Fail"End IfNext
ObjWorkbook.SaveObjExcel.QuitSet ObjWorksheet=NothingSet ObjWorkbook=NothingSet 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,ObjWorksheetDim oButton,Buttons,myButton,i
Set ObjExcel=CreateObject("Excel.Application")
www.gcreddy.com 10
www.gcreddy.com
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.CreateoButton("Class Name").value="WinButton"Set Buttons=Dialog("text:=Login").ChildObjects(oButton)
For i=0 to Buttons.Count-1 step 1myButton=Buttons(i).GetRoProperty("text")ObjWorksheet.Cells(i+2, 2)=myButton
Nextrows_Count= ObjWorksheet.usedrange.rows.count
For j= 2 to rows_Count step 1Expected=ObjWorksheet.Cells(j, 1)
For k=2 to rows_Count step 1Actual=ObjWorksheet.Cells(k, 2)
If Expected=Actual Then Flag =1 Exit for else Flag= 0
End Ifnext
If Flag=1 ThenObjWorksheet.Cells(j, 3)="Pass"ElseObjWorksheet.Cells(j, 3)="Fail"End IfNext
ObjWorkbook.SaveObjExcel.QuitSet ObjWorksheet=NothingSet ObjWorkbook=NothingSet ObjExcel=Nothing
-------------------------------------------------------------------13) Many to Many Textual Comparison
www.gcreddy.com 11
www.gcreddy.com
'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
Dim ObjExcel,ObjWorkbook,ObjWorksheetDim oButton,Buttons,myButton,i
Set ObjExcel=CreateObject("Excel.Application")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.CreateoButton("Class Name").value="WinButton"Set Buttons=Dialog("text:=Login").ChildObjects(oButton)
For i=0 to Buttons.Count-1 step 1myButton=Buttons(i).GetRoProperty("text")ObjWorksheet.Cells(i+2, 2)=myButton
Nextrows_Count= ObjWorksheet.usedrange.rows.count
For j= 2 to rows_Count step 1Expected=ObjWorksheet.Cells(j, 1)
For k=2 to rows_Count step 1Actual=ObjWorksheet.Cells(k, 2)
If StrComp (Expected,Actual,1)= 0 Then Flag =1 Exit for else Flag= 0
End Ifnext
If Flag=1 ThenObjWorksheet.Cells(j, 3)="Pass"ElseObjWorksheet.Cells(j, 3)="Fail"
www.gcreddy.com 12
www.gcreddy.com
End IfNext
ObjWorkbook.SaveObjExcel.QuitSet ObjWorksheet=NothingSet ObjWorkbook=NothingSet ObjExcel=Nothing---------------------------------------------------------------------------------------
14) 'Create Excel file and Rename 1st sheet as "Module", 2nd Sheet as "Test Case", 'and 3rd Sheet as "Test Step"Dim objExcelSet objExcel=CreateObject("Excel.Application")objExcel.Visible=TrueobjExcel.Workbooks.Add objExcel.Worksheets("Sheet1").Name="Module"Wait 4objExcel.Worksheets("Sheet2").Name="TestCase"Wait 4objExcel.Worksheets("Sheet3").Name="TestStep"
objExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\Administrator\Desktop\abcd.xls"
objExcel.QuitSet objExcel=Nothing-------------------------------------------------------------------------15) 'Create an Excel file and add one more
Dim objExcelSet objExcel=CreateObject("Excel.Application")objExcel.Visible=TrueobjExcel.Workbooks.Add 'Creating Work BookobjExcel.Worksheets.Add 'Creating Work SheetWait 4objExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\Administrator\Desktop\abcde.xls"
objExcel.QuitSet objExcel=Nothing--------------------------------------------------------------------------------------15) 'Capture Button names from Login Dialog (Flight Reservation Application) and perform Many to Many Complete Comparison
www.gcreddy.com 13
www.gcreddy.com
Capture Button names from Google home page and export to Excel file 3rd sheet
Dim ObjExcel,ObjWorkbook,ObjWorksheetDim oButton,Buttons,myButton,i
Set ObjExcel=CreateObject("Excel.Application")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.CreateoButton("Class Name").value="WinButton"Set Buttons=Dialog("text:=Login").ChildObjects(oButton)
For i=0 to Buttons.Count-1 step 1myButton=Buttons(i).GetRoProperty("text")ObjWorksheet.Cells(i+2, 2)=myButton
Nextrows_Count= ObjWorksheet.usedrange.rows.count
x =0
For j= 2 to rows_Count step 1Expected=ObjWorksheet.Cells(j, 1)flag = 0For k=2 to rows_Count step 1
Actual=ObjWorksheet.Cells(k, 2)If StrComp (Expected,Actual,1)= 0 ThenFlag =1End Ifx=x+1 ' increment the comparison count
next
If Flag=1 ThenObjWorksheet.Cells(j, 3)="Pass"
ElseObjWorksheet.Cells(j, 3)="Fail"
End Ifmsgbox x 'inner loop comparison values
Nextmsgbox x ' Total number of comparisons
ObjWorkbook.Save
www.gcreddy.com 14
www.gcreddy.com
ObjExcel.QuitSet ObjWorksheet=NothingSet ObjWorkbook=NothingSet ObjExcel=Nothing
www.gcreddy.com 15