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 Settings\bannu\Desktop\qtp.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 Settings\bannu\Desktop\qtp.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 Settings\bannu\Desktop\qtp.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 Settings\bannu\Desktop\qtp.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 Settings\bannu\Desktop\qtp.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 Settings\bannu\Desktop\qtp2.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 Settings\bannu\Desktop\abcd.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 Settings\bannu\Desktop\VBS.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 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").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 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")=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 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")=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 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")=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
--------------------------------------------------------------------

0 comments:

Post a Comment