Excel Scripting

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

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 book
Dim objExcel
objExcel.Visible=True 'To view the Operations
objExcel.Workbooks.Add 'Creatining Excel file / workbook
objExcel.ActiveWorkbook.SaveAs "C:Documents and
SettingsAdministratorDesktopgcreddy.xls"

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

-----------------------------------------------------------

www.gcreddy.com 2

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
SettingsAdministratorDesktopgcreddy.xls"
Set objFso=CreateObject("Scripting.FileSystemObject")

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
------------------------------------------------------

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
SettingsAdministratorDesktopinput.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 FilesHPQuickTest
Professionalsamplesflightappflight4a.exe","","C:Program
FilesHPQuickTest Professionalsamplesflightapp","open"

www.gcreddy.com 3

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
-------------------------------------------------------------------------

'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
Dialog("Login").WinEdit("Agent Name:").Set objWorksheet.Cells(i,"A")
Dialog("Login").WinEdit("Password:").Set objWorksheet.Cells(i,"B")
Wait 1

If Window("Flight Reservation").Exist(12) Then
objWorksheet.Cells(i,"C")="Login Successful"

www.gcreddy.com 4

www.gcreddy.com
Else
SystemUtil.CloseDescendentProcesses
objWorksheet.Cells(i,"C")="Login Filed"
End If

Next
objWorkbook.Save
objExcel.Quit


'Export Test Results & Error Messgae to the same file
Dim objExcel, objWorkbook, objWorksheet, rows_Count

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")

objWorksheet.Cells(i, 3)="Login Successful"
Else
objWorksheet.Cells(i, 3)="Login Failed"

www.gcreddy.com 5

www.gcreddy.com
objWorksheet.Cells(i, 4)=Dialog("Login").Dialog("Flight
Reservations").Static("Agent name must be at").GetROProperty ("text")
End If
Next

objWorkbook.Save
objExcel.Quit
6)
Using While...Wend Loop
------------------------
Dim objExcel, objWorkbook, objWorksheet, rows_Count, i

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")

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")

www.gcreddy.com 6

www.gcreddy.com
End If
i=i+1
Wend

objWorkbook.Save
objExcel.Quit

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

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

www.gcreddy.com

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

Dim oButton,Buttons,myButton,i


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
----------------------------------------------------------------------------------
-
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
SettingsgcrDesktopSample.xls")
Set objWorkSheet = objWorkBook.Worksheets(2)
objWorkSheet.cells(1,1) = "Order No."
objWorkSheet.cells(1,2) = "C-Name"

www.gcreddy.com 8

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"
Order").WinEdit("Edit").Set ord
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

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



ObjWorksheet.Cells(1,2)="Buttons"


ObjWorksheet.Cells(i+2, 2)=myButton

www.gcreddy.com 9

www.gcreddy.com
Next
rows_Count= ObjWorksheet.usedrange.rows.count
For j= 2 to rows_Count step 1
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

11) One to One Textual Comparison
------------------------------
Application) and Perform and Perform One to One Textual Comparison





Next

www.gcreddy.com 10

www.gcreddy.com
Actual=ObjWorksheet.Cells(j, 2)

If StrComp (Expected,Actual,1)=0 Then
Else
End If
Next

ObjWorkbook.Save
ObjExcel.Quit
--------------------------------------------------------------------------
12) Many to Many Comparison
-----------------------------------
Application) and Perform and Perform Many to Many Comparison





Next


www.gcreddy.com 11

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
Else
End If
Next

ObjWorkbook.Save
ObjExcel.Quit

-------------------------------------------------------------------
13) Many to Many Textual Comparison

Application) and Perform and Perform Many to Many Textual
Comparison

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




www.gcreddy.com 12

www.gcreddy.com


Next



If StrComp (Expected,Actual,1)= 0 Then
Flag =1
Exit for
else
Flag= 0
End If
next

If Flag=1 Then
Else
End If
Next

ObjWorkbook.Save
ObjExcel.Quit
----------------------------------------------------------------------------------
-----

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

www.gcreddy.com 13

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"

SettingsAdministratorDesktopabcd.xls"

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

Dim objExcel
objExcel.Visible=True
objExcel.Workbooks.Add 'Creating Work Book
objExcel.Worksheets.Add 'Creating Work Sheet
Wait 4
SettingsAdministratorDesktopabcde.xls"

objExcel.Quit
----------------------------------------------------------------------------------
----
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




www.gcreddy.com 14

www.gcreddy.com


Next

x =0

flag = 0
If StrComp (Expected,Actual,1)= 0 Then
Flag =1
End If
x=x+1 ' increment the comparison count
next

If Flag=1 Then
Else
End If
msgbox x 'inner loop comparison values
Next
msgbox x ' Total number of comparisons

ObjWorkbook.Save
ObjExcel.Quit

www.gcreddy.com 15

Excel Scripting

Recommended

More Related Content

What's hot (19)

Similar to Excel Scripting (20)

More from G C Reddy Technologies (8)

Recently uploaded (20)

Excel Scripting