QTP Excel Scripts

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 Files
Set objExcel=CreateObject(“Excel.Application”)
Examples:
1) Create an Excel file
Dim objExcel
Set objExcel=CreateObject(“Excel.Application”)
objExcel.Visible=True ‘To make the Operations visible
objExcel.Workbooks.Add
objExcel.ActiveWorkbook.SaveAs “C:Documents and SettingsbannuDesktopqtp.xls”
objExcel.Quit
Set objExcel=Nothing
2) Check existence of the File, If not exist then create the file
Dim objExcel, objFso, FilePath
FilePath=”C:Documents and SettingsbannuDesktopqtp.xls”
Set objFso=CreateObject(“Scripting.FileSystemObject”)
If Not objFso.FileExists(FilePath) Then
Set objExcel=CreateObject(“Excel.Application”)
objExcel.Workbooks.Add
objExcel.ActiveWorkbook.SaveAs (FilePath)
objExcel.Quit
Set objExcel=Nothing
End if
Set objFso=Nothing
——————
a) Excel Application Object
b) Excel Application
c) Excel File / WorkBook
d) 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, FilePath
FilePath=”C:Documents and SettingsbannuDesktopqtp.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).value=”QTP Tool”
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close
Else
objExcel.Workbooks.Add
objExcel.Worksheets(“Sheet1″).Cells(1,1).Value=”QTP Tool”
objExcel.ActiveWorkbook.SaveAs(FilePath)
End if
objExcel.Quit
Set objExcel=Nothing
Set objFso=Nothing
4) ‘Capture Link Names from Google Home page and export to an Excel file (Sheet2)
Dim objExcel, FilePath, objWorkBook, objSheet,r
Dim oLink, Links, i, myLink
FilePath=”C:Documents and SettingsbannuDesktopqtp.xls”
‘Creating Excel Application Object
Set objExcel=CreateObject(“Excel.Application”)
‘Creating WorkBook Object
Set objWorkBook=objExcel.Workbooks.Open(FilePath)
‘Creating WorkSheet Object
Set objSheet=objWorkBook.Worksheets(“Sheet2″)
r=1
objSheet.Cells(r,”A”)=”Link Names”
Set oLink=Description.Create
oLink(“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”)=myLink
Next
objWorkBook.Save
objWorkBook.Close
objExcel.Quit
Set 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 SettingsbannuDesktopqtp.xls”
‘Creating Excel Application Object
Set objExcel=CreateObject(“Excel.Application”)
‘Creating WorkBook Object
Set objWorkBook=objExcel.Workbooks.Open(FilePath)
‘Creating WorkSheet Object
Set objSheet=objWorkBook.Worksheets(“Sheet3″)
r=1
objSheet.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”)=ord
objSheet.Cells(r,”B”)=C_Name
Next
objWorkBook.Save
objWorkBook.Close
objExcel.Quit
Set objExcel=Nothing
6) ‘Create an Excel file and Add one more sheet
Dim objExcel
Set objExcel=CreateObject(“Excel.Application”)
objExcel.Workbooks.Add
objExcel.Worksheets.Add
objExcel.ActiveWorkbook.SaveAs “C:Documents and SettingsbannuDesktopqtp2.xls”
objExcel.Quit
Set objExcel=Nothing
7) ‘Open an Excel file and Move 1st sheet to 3 rd Position
Dim objExcel
Set objExcel=CreateObject(“Excel.Application”)
objExcel.Workbooks.Open (“C:Documents and SettingsbannuDesktopabcd.xls”)
objExcel.Worksheets(“Sheet1″).Move,objExcel.Worksheets(“Sheet3″)
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close
objExcel.Quit
Set 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 SettingsbannuDesktopVBS.xls”)
objExcel.ActiveWorkbook.Close
objExcel.Quit
Set 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 SettingsbannuDesktopVBS.xls”)
Set objWorkSheet=objWorkBook.Worksheets(“Module”)
Rows_Count=objWorkSheet.Usedrange.rows.count
For i= 2 to Rows_Count Step 1
    SystemUtil.Run “C:Program FilesHPQuickTest Professionalsamplesflightappflight4a.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”).Close
Next
objWorkBook.Close
Set objWorkSheet=Nothing
Set objWorkBook=Nothing
objExcel.Quit
Set 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 SettingsbannuDesktopVBS.xls”)
Set objWorkSheet=objWorkBook.Worksheets(“Module”)
Rows_Count=objWorkSheet.Usedrange.rows.count
For i= 2 to Rows_Count Step 1
    SystemUtil.Run “C:Program FilesHPQuickTest Professionalsamplesflightappflight4a.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”)=Result
Else
Systemutil.CloseDescendentProcesses
Result=”Login Failed”
objWorkSheet.Cells(i,”C”)=Result
End If
Next
objWorkBook.Save
objWorkBook.Close
Set objWorkSheet=Nothing
Set objWorkBook=Nothing
objExcel.Quit
Set 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 SettingsbannuDesktopVBS.xls”)
Set objWorkSheet=objWorkBook.Worksheets(“Module”)
Rows_Count=objWorkSheet.Usedrange.rows.count
For i= 2 to Rows_Count Step 1
    SystemUtil.Run “C:Program FilesHPQuickTest Professionalsamplesflightappflight4a.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”)=Result
Else
Error_Message = Dialog(“text:=Login”).Dialog(“text:=Flight Reservations”).Static(“text:=Agent name must be at least 4 characters long.”).GetROProperty(“text”)
Systemutil.CloseDescendentProcesses
Result=”Login Failed”
objWorkSheet.Cells(i,”C”)=Result
objWorkSheet.Cells(i,”D”)=Error_Message
End If
Next
objWorkBook.Save
objWorkBook.Close
Set objWorkSheet=Nothing
Set objWorkBook=Nothing
objExcel.Quit
Set 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 SettingsbannuDesktopVBS.xls”)
Set objWorkSheet=objWorkBook.Worksheets(“Module”)
Rows_Count=objWorkSheet.Usedrange.rows.count
For i= 2 to Rows_Count Step 1
    SystemUtil.Run “C:Program FilesHPQuickTest Professionalsamplesflightappflight4a.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”)=Result
Else
Error_Message = Dialog(“text:=Login”).Dialog(“text:=Flight Reservations”).Static(“window id:=65535″).GetROProperty(“text”)
Systemutil.CloseDescendentProcesses
Result=”Login Failed”
objWorkSheet.Cells(i,”C”)=Result
objWorkSheet.Cells(i,”D”)=Error_Message
End If
Next
objWorkBook.Save
objWorkBook.Close
Set objWorkSheet=Nothing
Set objWorkBook=Nothing
objExcel.Quit
Set objExcel=Nothing
——————————————————————–

 

Comments