Excel Scripting


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 Object

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

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
Set objExcel=CreateObject("Excel.Application")
objExcel.Visible=True 'To view the Operations
objExcel.Workbooks.Add 'Creatining Excel file / workbook
objExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\Administrator\Desktop\gcreddy.xls"

objExcel.Quit 'To Quit the Excel Application
Set 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
Dim objExcel, objFso, FilePath
FilePath="C:\Documents and Settings\Administrator\Desktop\gcreddy.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)="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
Set 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 Appliction
Set objExcel=CreateObject("Excel.Application")
'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 Settings\Administrator\Desktop\input.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 Files\HP\QuickTest Professional\samples\flight\app\flight4a.exe","","C:\Program Files\HP\QuickTest Professional\samples\flight\app\","open"
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
Set 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 file
Dim objExcel, objWorkbook, objWorksheet
'Create Excel application Object that can be used to perform operations on Excel Appliction
Set objExcel=CreateObject("Excel.Application")
'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 Settings\Administrator\Desktop\input.xls")
'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
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").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

If Window("Flight Reservation").Exist(12) Then
        Window("Flight Reservation").Close
        objWorksheet.Cells(i,"C")="Login Successful"
        Else
SystemUtil.CloseDescendentProcesses
objWorksheet.Cells(i,"C")="Login Filed"
End If

Next
objWorkbook.Save
objExcel.Quit
Set objWorksheet=Nothing
Set objWorkbook=Nothing
Set 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 file
Dim 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 1
        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").Activate
        Dialog("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) Then
                Window("Flight Reservation").Close
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")
SystemUtil.CloseDescendentProcesses
End If
Next

objWorkbook.Save
objExcel.Quit
Set objWorksheet=Nothing
Set objWorkbook=Nothing
Set objExcel=Nothing
6)
Using While...Wend Loop
------------------------
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").Activate
        Dialog("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) Then
                Window("Flight Reservation").Close
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")
SystemUtil.CloseDescendentProcesses
End If
i=i+1
Wend

objWorkbook.Save
objExcel.Quit
Set objWorksheet=Nothing
Set objWorkbook=Nothing
Set objExcel=Nothing

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 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,ObjWorksheet
Dim 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.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
Set ObjWorksheet=Nothing
Set ObjWorkbook=Nothing
Set ObjExcel=Nothing
-----------------------------------------------------------------------------------
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 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 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 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
Set objExcel=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

Dim ObjExcel,ObjWorkbook,ObjWorksheet
Dim 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.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, 2)=myButton
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
Set ObjWorksheet=Nothing
Set ObjWorkbook=Nothing
Set 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

Dim ObjExcel,ObjWorkbook,ObjWorksheet
Dim 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.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, 2)=myButton
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  StrComp (Expected,Actual,1)=0 Then
ObjWorksheet.Cells(j, 3)="Pass"
Else
ObjWorksheet.Cells(j, 3)="Fail"
End If
Next

ObjWorkbook.Save
ObjExcel.Quit
Set ObjWorksheet=Nothing
Set ObjWorkbook=Nothing
Set 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,ObjWorksheet
Dim 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.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, 2)=myButton
Next
rows_Count= ObjWorksheet.usedrange.rows.count

For j= 2 to rows_Count step 1
Expected=ObjWorksheet.Cells(j, 1)

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
ObjWorksheet.Cells(j, 3)="Pass"
Else
ObjWorksheet.Cells(j, 3)="Fail"
End If
Next

ObjWorkbook.Save
ObjExcel.Quit
Set ObjWorksheet=Nothing
Set ObjWorkbook=Nothing
Set ObjExcel=Nothing

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

'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,ObjWorksheet
Dim 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.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, 2)=myButton
Next
rows_Count= ObjWorksheet.usedrange.rows.count

For j= 2 to rows_Count step 1
Expected=ObjWorksheet.Cells(j, 1)

For k=2 to rows_Count step 1
        Actual=ObjWorksheet.Cells(k, 2)

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

If  Flag=1 Then
ObjWorksheet.Cells(j, 3)="Pass"
Else
ObjWorksheet.Cells(j, 3)="Fail"
End If
Next

ObjWorkbook.Save
ObjExcel.Quit
Set ObjWorksheet=Nothing
Set ObjWorkbook=Nothing
Set ObjExcel=Nothing
---------------------------------------------------------------------------------------

14) 'Create Excel file and Rename 1st sheet as "Module", 2nd Sheet as "Test Case", 'and 3rd Sheet as "Test Step"
Dim objExcel
Set objExcel=CreateObject("Excel.Application")
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"

objExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\Administrator\Desktop\abcd.xls"

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

Dim objExcel
Set objExcel=CreateObject("Excel.Application")
objExcel.Visible=True
objExcel.Workbooks.Add 'Creating Work Book
objExcel.Worksheets.Add 'Creating Work Sheet
Wait 4
objExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\Administrator\Desktop\abcde.xls"

objExcel.Quit
Set objExcel=Nothing
--------------------------------------------------------------------------------------
16) '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

Dim ObjExcel,ObjWorkbook,ObjWorksheet
Dim 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.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, 2)=myButton
Next
rows_Count= ObjWorksheet.usedrange.rows.count

x =0

For j= 2 to rows_Count step 1
        Expected=ObjWorksheet.Cells(j, 1)
        flag  = 0
        For k=2 to rows_Count step 1
                Actual=ObjWorksheet.Cells(k, 2)
                If  StrComp (Expected,Actual,1)= 0 Then
                Flag =1
                End If
                x=x+1 ' increment the comparison count
        next

        If  Flag=1 Then
                ObjWorksheet.Cells(j, 3)="Pass"
        Else
                ObjWorksheet.Cells(j, 3)="Fail"
        End If
        msgbox x  'inner loop comparison values
Next
        msgbox x ' Total number of comparisons

ObjWorkbook.Save
ObjExcel.Quit
Set ObjWorksheet=Nothing
Set ObjWorkbook=Nothing
Set ObjExcel=Nothing

0 comments:

Post a Comment