qtp/uft (working with excel)
DESCRIPTION
QTP (Working with Excel)TRANSCRIPT
Excel File Operations
Creating Excel Application Object
Dim objExcel'Creating an Automation Object in Excel Application class, that can be used to perform operation on Excel FilesSet objExcel=CreateObject("Excel.Application")
1) Create an Excel file
Dim objExcelSet objExcel=CreateObject("Excel.Application")objExcel.Visible=True 'To make the Operations visibleobjExcel.Workbooks.AddobjExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\bannu\Desktop\qtp.xls"
objExcel.QuitSet objExcel=Nothing
2) Check existence of the File, If not exist then create the file
Dim objExcel, objFso, FilePathFilePath="C:\Documents and Settings\bannu\Desktop\qtp.xls"Set objFso=CreateObject("Scripting.FileSystemObject")
If Not objFso.FileExists(FilePath) ThenSet objExcel=CreateObject("Excel.Application")objExcel.Workbooks.AddobjExcel.ActiveWorkbook.SaveAs (FilePath)objExcel.QuitSet objExcel=NothingEnd if
Set objFso=Nothing------------------
a) Excel Application Objectb) Excel Applicationc) Excel File / WorkBookd) Excel Sheet
3) 'Check existence of the File, If exist then Enter some data'If not Exist then Create the filr and enter some data
Dim objExcel, objFso, FilePathFilePath="C:\Documents and Settings\bannu\Desktop\qtp.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).value="QTP Tool"objExcel.ActiveWorkbook.SaveobjExcel.ActiveWorkbook.CloseElseobjExcel.Workbooks.AddobjExcel.Worksheets("Sheet1").Cells(1,1).Value="QTP Tool"objExcel.ActiveWorkbook.SaveAs(FilePath)End if
objExcel.QuitSet objExcel=NothingSet objFso=Nothing
4) 'Capture Link Names from Google Home page and export to an Excel file (Sheet2)
Dim objExcel, FilePath, objWorkBook, objSheet,rDim oLink, Links, i, myLinkFilePath="C:\Documents and Settings\bannu\Desktop\qtp.xls"
'Creating Excel Application Object
Set objExcel=CreateObject("Excel.Application")
'Creating WorkBook ObjectSet objWorkBook=objExcel.Workbooks.Open(FilePath)
'Creating WorkSheet ObjectSet objSheet=objWorkBook.Worksheets("Sheet2")
r=1objSheet.Cells(r,"A")="Link Names"
Set oLink=Description.CreateoLink("micclass").Value="Link"Set Links=Browser("name:=Google").Page("title:=Google").ChildObjects(oLink)
For i= 0 to Links.Count -1 Step 1 r=r+1 myLink=Links(i).GetRoProperty("text")objSheet.Cells(r,"A")=myLinkNext
objWorkBook.SaveobjWorkBook.CloseobjExcel.QuitSet objExcel=Nothing
5) 'Capture Customer Names from the FR window and export to an Excel file (Sheet3)
Dim objExcel, FilePath, objWorkBook, objSheet,r, ord
FilePath="C:\Documents and Settings\bannu\Desktop\qtp.xls"
'Creating Excel Application ObjectSet objExcel=CreateObject("Excel.Application")
'Creating WorkBook ObjectSet objWorkBook=objExcel.Workbooks.Open(FilePath)
'Creating WorkSheet Object
Set objSheet=objWorkBook.Worksheets("Sheet3")
r=1objSheet.Cells(r,"A")="OrderNo"objSheet.Cells(r,"B")="C_Names"
For ord= 1 to 10 Step 1 r=r+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 2 C_Name = Window("Flight Reservation").WinEdit("Name:").GetROProperty("text")objSheet.Cells(r,"A")=ordobjSheet.Cells(r,"B")=C_NameNext
objWorkBook.SaveobjWorkBook.CloseobjExcel.QuitSet objExcel=Nothing
6) 'Create an Excel file and Add one more sheet
Dim objExcelSet objExcel=CreateObject("Excel.Application")objExcel.Workbooks.AddobjExcel.Worksheets.AddobjExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\bannu\Desktop\qtp2.xls"objExcel.QuitSet objExcel=Nothing
7) 'Open an Excel file and Move 1st sheet to 3 rd Position
Dim objExcelSet objExcel=CreateObject("Excel.Application")objExcel.Workbooks.Open ("C:\Documents and Settings\bannu\Desktop\abcd.xls")
objExcel.Worksheets("Sheet1").Move,objExcel.Worksheets("Sheet3")objExcel.ActiveWorkbook.SaveobjExcel.ActiveWorkbook.CloseobjExcel.QuitSet objExcel=Nothing
8) ',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.Workbooks.Add
objExcel.Worksheets("Sheet1").Name="Module"objExcel.Worksheets("Sheet2").Name="TestCase"objExcel.Worksheets("Sheet3").Name="TestStep"
objExcel.ActiveWorkbook.Saveas ("C:\Documents and Settings\bannu\Desktop\VBS.xls")objExcel.ActiveWorkbook.CloseobjExcel.QuitSet objExcel=Nothing
9) Data Driven Testing by Fetching Test Data from an Excel file
Dim objExcel, objWorkBook, objWorkSheet, i
Set objExcel=CreateObject("Excel.Application")Set objWorkBook=objExcel.Workbooks.Open ("C:\Documents and Settings\bannu\Desktop\VBS.xls")Set objWorkSheet=objWorkBook.Worksheets("Module")
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" Dialog("text:=Login").Activate Dialog("text:=Login").WinEdit("attached text:=Agent Name:").Set objWorkSheet.Cells(i,"A")
Dialog("text:=Login").WinEdit("attached text:=Password:").Set objWorkSheet.Cells(i,"B") Wait 2 Dialog("text:=Login").WinButton("text:=OK").Click Window("Flight Reservation").CloseNextobjWorkBook.CloseSet objWorkSheet=NothingSet objWorkBook=NothingobjExcel.QuitSet objExcel=Nothing----------------------------10) 'Data Driven Testing by Fetching Test Data from an Excel file and Export the Result to the Same file
Dim objExcel, objWorkBook, objWorkSheet, i
Set objExcel=CreateObject("Excel.Application")Set objWorkBook=objExcel.Workbooks.Open ("C:\Documents and Settings\bannu\Desktop\VBS.xls")Set objWorkSheet=objWorkBook.Worksheets("Module")
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" Dialog("text:=Login").Activate Dialog("text:=Login").WinEdit("attached text:=Agent Name:").Set objWorkSheet.Cells(i,"A") Dialog("text:=Login").WinEdit("attached text:=Password:").Set objWorkSheet.Cells(i,"B") Dialog("text:=Login").WinButton("text:=OK").Click If Window("text:=Flight Reservation").Exist(12) Then Window("text:=Flight Reservation").Close Result="Login Operation Sucessful"objWorkSheet.Cells(i,"C")=ResultElseSystemutil.CloseDescendentProcessesResult="Login Failed"objWorkSheet.Cells(i,"C")=ResultEnd IfNextobjWorkBook.Save
objWorkBook.CloseSet objWorkSheet=NothingSet objWorkBook=NothingobjExcel.QuitSet objExcel=Nothing
---------------------------11) Data Driven Testing by Fetching Test Data from an Excel file and Export the Result and Message to the Same file
Dim objExcel, objWorkBook, objWorkSheet, i, Result, Error_Message
Set objExcel=CreateObject("Excel.Application")Set objWorkBook=objExcel.Workbooks.Open ("C:\Documents and Settings\bannu\Desktop\VBS.xls")Set objWorkSheet=objWorkBook.Worksheets("Module")
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" Dialog("text:=Login").Activate Dialog("text:=Login").WinEdit("attached text:=Agent Name:").Set objWorkSheet.Cells(i,"A") Dialog("text:=Login").WinEdit("attached text:=Password:").Set objWorkSheet.Cells(i,"B") Dialog("text:=Login").WinButton("text:=OK").Click If Window("text:=Flight Reservation").Exist(12) Then Window("text:=Flight Reservation").Close Result="Login Operation Sucessful"objWorkSheet.Cells(i,"C")=ResultElseError_Message = Dialog("text:=Login").Dialog("text:=Flight Reservations").Static("text:=Agent name must be at least 4 characters long.").GetROProperty("text")Systemutil.CloseDescendentProcessesResult="Login Failed"objWorkSheet.Cells(i,"C")=ResultobjWorkSheet.Cells(i,"D")=Error_MessageEnd IfNext
objWorkBook.SaveobjWorkBook.CloseSet objWorkSheet=NothingSet objWorkBook=NothingobjExcel.QuitSet objExcel=Nothing--------------------------12) Data Driven Testing by Fetching Test Data from an Excel file and Export the Result and Message to the Same file
Dim objExcel, objWorkBook, objWorkSheet, i, Result, Error_Message
Set objExcel=CreateObject("Excel.Application")Set objWorkBook=objExcel.Workbooks.Open ("C:\Documents and Settings\bannu\Desktop\VBS.xls")Set objWorkSheet=objWorkBook.Worksheets("Module")
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" Dialog("text:=Login").Activate Dialog("text:=Login").WinEdit("attached text:=Agent Name:").Set objWorkSheet.Cells(i,"A") Dialog("text:=Login").WinEdit("attached text:=Password:").Set objWorkSheet.Cells(i,"B") Dialog("text:=Login").WinButton("text:=OK").Click If Window("text:=Flight Reservation").Exist(12) Then Window("text:=Flight Reservation").Close Result="Login Operation Sucessful"objWorkSheet.Cells(i,"C")=ResultElseError_Message = Dialog("text:=Login").Dialog("text:=Flight Reservations").Static("window id:=65535").GetROProperty("text")Systemutil.CloseDescendentProcessesResult="Login Failed"objWorkSheet.Cells(i,"C")=ResultobjWorkSheet.Cells(i,"D")=Error_MessageEnd IfNextobjWorkBook.Save
objWorkBook.CloseSet objWorkSheet=NothingSet objWorkBook=NothingobjExcel.QuitSet objExcel=Nothing