9:32 pm
Dim xlobj, linkobj, link_col
Set linkobj = Description.Create
linkobj("micclass").Value = "Link"
Set link_col = Browser("Internet Explorer Enhanced").Page("Internet Explorer Enhanced").ChildObjects (linkobj)
count = link_col.count
msgbox count
Set xlobj = CreateObject("Excel.Application")
xlobj.Visible = true
xlobj.Workbooks.Add
For i=0 to count-1
xlobj.Cells(i,"A")= link_col(i).GetROProperty("name")
xlobj.Cells(i,"B")= link_col(i).GetROProperty("url")
Next
xlobj.ActiveWorkbook.SaveAs "c:\testdata.xl"
xlobj.Quit
Set xlobj=nothing
************************************************************
Excel Application Operations
------------------------------------
'Create Excel Application Object
Dim objExcel
Set objExcel = CreateObject("Excel.application")
---------------------------------------
'Create an excel file
Dim objExcel
Set objExcel = CreateObject("Excel.application")
objExcel.Visible = True 'To view the Operations
objExcel.Workbooks.Add 'To create new workbook /file
objExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\Administrator\Desktop\Vbs.xls"
objExcel.Quit 'To close Excel application
Set objExcel = Nothing
--------------------------------------------------------------------------------------
'Check the Existance of the File, if not exist then Create the excel file
Dim objExcel, objFso
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.application")
If Not objFso.FileExists("C:\Documents and Settings\Administrator\Desktop\Vbs.xls") Then
objExcel.Workbooks.Add
objExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\Administrator\Desktop\Vbs.xls"
End If
objExcel.Quit
Set objExcel = Nothing
Set objFso = Nothing
----------------------------------------------------------------------------------------------------
'Check the Existance of the File if exists open the file and write some data, if not exist then Create the excel file and write some data
Dim objExcel, objFso, FilePath
FilePath = "C:\Documents and Settings\Administrator\Desktop\Vbs.xls"
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.application")
If objFso.FileExists(FilePath) Then
objExcel.Workbooks.Open (FilePath)
objExcel.Worksheets(1).Cells(1,1)="QTP"
objExcel.ActiveWorkbook.Save
Else
objExcel.Workbooks.Add
objExcel.Worksheets(1).Cells(1,1)="QTP"
objExcel.ActiveWorkbook.SaveAs FilePath
End If
objExcel.Quit
Set objExcel = Nothing
Set objFso = Nothing
---------------------------------------------------------------------------------
'Check the Existance of the File if exists open the file and write some data, if not exist then Create the excel file and write some data
Dim objExcel, objFso, FilePath, objWorkbook, objWorksheet
FilePath = "C:\Documents and Settings\Administrator\Desktop\Vbs.xls"
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.application")
If objFso.FileExists(FilePath) Then
Set objWorkbook = objExcel.Workbooks.Open (FilePath)
Set objWorksheet = objWorkbook.Worksheets(2)
objWorksheet.Cells(2,2)="VB script"
objWorkbook.Save
Else
objExcel.Workbooks.Add
objExcel.Worksheets(2).Cells(2,2)="VB script"
objExcel.ActiveWorkbook.SaveAs FilePath
End If
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook=Nothing
Set objExcel = Nothing
Set objFso = Nothing
------------------------------------------------------------------------------------------------------
'Read test data from an excel file and perform Data driven Testing for Login Operation
Dim objExcel, objWorkbook, objWorksheet, row_Count
'Create Excel Application Object, used to perform oprations on Excel Application
Set objExcel = CreateObject("Excel.Application")
'Create Excel WorkBook Object, used to perform oprations on Excel Work books /files
Set objWorkbook = objExcel.Workbooks.Open ("C:\Documents and Settings\Administrator\Desktop\input.xls")
'Create Excel WorkSheet Object, used to perform oprations on Excel Sheets
Set objWorksheet = objWorkbook.Worksheets("Sheet1")
row_Count = objWorksheet.usedrange.rows.count
For i = 2 to row_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")
wait 1
Dialog("Login").WinButton("OK").Click
Window("Flight Reservation").Close
Next
objWorkbook.Close
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
-----------------------------------------------------------------------------------------------
'Read test data from an excel file and perform Data driven Testing for Login Operation
'Export Results to the Same file
Dim objExcel, objWorkbook, objWorksheet, row_Count
'Create Excel Application Object, used to perform oprations on Excel Application
Set objExcel = CreateObject("Excel.Application")
'Create Excel WorkBook Object, used to perform oprations on Excel Work books /files
Set objWorkbook = objExcel.Workbooks.Open ("C:\Documents and Settings\Administrator\Desktop\input.xls")
'Create Excel WorkSheet Object, used to perform oprations on Excel Sheets
Set objWorksheet = objWorkbook.Worksheets("Sheet1")
row_Count = objWorksheet.usedrange.rows.count
objWorksheet.Cells(1,3)="Test Result"
For i = 2 to row_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")
wait 1
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"
SystemUtil.CloseDescendentProcesses
End If
Next
objWorkbook.Save
objWorkbook.Close
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
------------------------------------------------------------------------------------------------------------
'Read test data from an excel file and perform Data driven Testing for Login Operation
'Export Results and Error messages to the Same file
Dim objExcel, objWorkbook, objWorksheet, row_Count
'Create Excel Application Object, used to perform oprations on Excel Application
Set objExcel = CreateObject("Excel.Application")
'Create Excel WorkBook Object, used to perform oprations on Excel Work books /files
Set objWorkbook = objExcel.Workbooks.Open ("C:\Documents and Settings\Administrator\Desktop\input.xls")
'Create Excel WorkSheet Object, used to perform oprations on Excel Sheets
Set objWorksheet = objWorkbook.Worksheets("Sheet1")
row_Count = objWorksheet.usedrange.rows.count
objWorksheet.Cells(1,3)="Test Result"
objWorksheet.Cells(1,4)="Error Message"
For i = 2 to row_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")
wait 1
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,4) = Dialog("Login").Dialog("Flight Reservations").Static("Agent name must be at").GetROProperty("text")
objWorksheet.Cells(i, 3)="Login Failed"
SystemUtil.CloseDescendentProcesses
End If
Next
objWorkbook.Save
objWorkbook.Close
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
------------------------------------------------------------------------------------------------------
====================
'Export Links from Google Page to Excel workbook 2nd sheet
Dim objExcel, objWorkbook,objWorksheet
Dim oLink, Links, i, filepath
filepath="C:\Documents and Settings\Administrator\Desktop\input.xls"
Set objExcel=createobject("Excel.Application")
Set objWorkbook=objExcel.Workbooks.Open(filepath)
Set objWorksheet=objWorkbook.Worksheets("Sheet2")
Set oLink=description.Create
oLink("micclass").value="Link"
Set Links=Browser("title:=Google").page("title:=Google").ChildObjects(oLink)
objWorksheet.cells(1,"A")="Link Names"
For i=0 to Links.count-1 Step 1
objWorksheet.cells(i+2,"A")=Links(i).getroproperty("text")
Next
objWorkbook.Save
objWorkbook.Close
objExcel.Quit
Set objWorksheet=Nothing
Set objWorkbook=Nothing
Set objExcel=Nothing
------------------------------------------------------------------------------------------------------
''Read button Names from Login dialog and compare with expected values in Excel sheet (One to One exact match)
Dim objExcel, objWorkbook,objWorksheet
Dim oButton, Buttons, i, filepath
filepath="C:\Documents and Settings\Administrator\Desktop\input.xls"
Set objExcel=createobject("Excel.Application")
Set objWorkbook=objExcel.Workbooks.Open(filepath)
Set objWorksheet=objWorkbook.Worksheets("Sheet3")
Set oButton=description.Create
oButton("micclass").value="WinButton"
Set Buttons=Dialog("text:=Login").ChildObjects(oButton)
objWorksheet.cells(1,"B")="Actual"
objWorksheet.cells(1,"C")="Results"
For i=0 to Buttons.count-1 Step 1
objWorksheet.cells(i+2,"B")=Buttons(i).Getroproperty("text")
Next
objWorkbook.Save
Rows_Count=objWorksheet.usedrange.rows.count
For j= 2 to Rows_Count Step 1
Expected=objWorksheet.cells(j,"A")
Actual = objWorksheet.cells(j, "B")
If Expected = Actual Then
objWorksheet.cells(j,"C")= "Passed"
Else
objWorksheet.cells(j,"C")= "Failed"
End If
Next
objWorkbook.Save
objWorkbook.Close
objExcel.Quit
Set objWorksheet=Nothing
Set objWorkbook=Nothing
Set objExcel=Nothing
--------------------------------------------------------------------------------
''Read button Names from Login dialog and compare with expected values in Excel sheet (One to One Texual Comparision)
Dim objExcel, objWorkbook,objWorksheet
Dim oButton, Buttons, i, filepath
filepath="C:\Documents and Settings\Administrator\Desktop\input.xls"
Set objExcel=createobject("Excel.Application")
Set objWorkbook=objExcel.Workbooks.Open(filepath)
Set objWorksheet=objWorkbook.Worksheets("Sheet3")
Set oButton=description.Create
oButton("micclass").value="WinButton"
Set Buttons=Dialog("text:=Login").ChildObjects(oButton)
objWorksheet.cells(1,"B")="Actual"
objWorksheet.cells(1,"C")="Results"
For i=0 to Buttons.count-1 Step 1
objWorksheet.cells(i+2,"B")=Buttons(i).Getroproperty("text")
Next
objWorkbook.Save
Rows_Count=objWorksheet.usedrange.rows.count
For j= 2 to Rows_Count Step 1
Expected=objWorksheet.cells(j,"A")
Actual = objWorksheet.cells(j, "B")
If StrComp (Expected, Actual, 1) = 0 Then
objWorksheet.cells(j,"C")= "Passed"
Else
objWorksheet.cells(j,"C")= "Failed"
End If
Next
objWorkbook.Save
objWorkbook.Close
objExcel.Quit
Set objWorksheet=Nothing
Set objWorkbook=Nothing
Set objExcel=Nothing
--------------------------------------------------------------------------------
''Read button Names from Login dialog and compare with expected values in Excel sheet (Many to Many Texual Comparision)
Dim objExcel, objWorkbook,objWorksheet
Dim oButton, Buttons, i, filepath
filepath="C:\Documents and Settings\Administrator\Desktop\input.xls"
Set objExcel=createobject("Excel.Application")
Set objWorkbook=objExcel.Workbooks.Open(filepath)
Set objWorksheet=objWorkbook.Worksheets("Sheet3")
Set oButton=description.Create
oButton("micclass").value="WinButton"
Set Buttons=Dialog("text:=Login").ChildObjects(oButton)
objWorksheet.cells(1,"B")="Actual"
objWorksheet.cells(1,"C")="Results"
For i=0 to Buttons.count-1 Step 1
objWorksheet.cells(i+2,"B")=Buttons(i).Getroproperty("text")
Next
objWorkbook.Save
Rows_Count=objWorksheet.usedrange.rows.count
For j= 2 to Rows_Count Step 1
Expected=objWorksheet.cells(j,"A")
For k= 2 to Rows_Count Step 1
Actual = objWorksheet.cells(k, "B")
Flag=0
If StrComp(Expected, Actual, 1)=0 Then
Flag=1
Exit For
Else
Flag=0
End If
Next
If Flag = 1 Then
objWorksheet.cells(k, "C") = "Passed"
Else
objWorksheet.cells(k, "C") = "Failed"
End If
Next
objWorkbook.Save
objWorkbook.Close
objExcel.Quit
Set objWorksheet=Nothing
Set objWorkbook=Nothing
Set objExcel=Nothing
---------------------------------------------------------------------------------------------------------
'Create an excel workbook / file and rename 1st sheet as "Module", 2nd sheet as "TestCase", and 3rd sheet as "Test Step
Dim objExcel
Set objExcel = Createobject("Excel.Application")
objExcel.Workbooks.Add
objExcel.Visible = True
objExcel.Worksheets(1).Name ="Module"
wait 2
objExcel.Worksheets(2).Name ="TestCase"
wait 2
objExcel.Worksheets(3).Name ="TestStep"
objExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\Administrator\Desktop\dec.xls"
objExcel.Quit
Set objExcel = Nothing
--------------------------------------------------------------------------------
'Create an excel workbook / file and Add one more sheet
Dim objExcel
Set objExcel = Createobject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
Wait 2
objExcel.Worksheets.Add
objExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\Administrator\Desktop\dec.xls"
objExcel.Quit
Set objExcel = Nothing
-------------------------
Search for a particular value in Excel
Set appExcel = CreateObject(“Excel.Application”)
appExcel.visible=true
Set objWorkBook = appExcel.Workbooks.Open (filepath)’opens the sheet
Set objSheet = appExcel.Sheets(“Sheet1?)’ To select particular sheet
With objSheet.UsedRange ‘ select the used range in particular sheet
Set c = .Find (“nn”)’ data to find
For each c in objSheet.UsedRange’ Loop through the used range
If c=”nn” then’ compare with the expected data
c.Interior.ColorIndex = 40' make the gary color if it finds the data
End If
Set c = .FindNext(c)’ next search
next
End With
objWorkBook.save
objWorkBook.close
set appExcel=nothing
Author:Mohan Kakarla
Q2. Write a script in QTP to import data from an external data sheet using Excel application File System Object?
'Script to insert an order and print the Order Number
'Create an object to interact with Excel Library
Set xlapp = CreateObject("Excel.Application")
'Open the excel workbook
Set wbook = xlapp.workbooks.Open("E:\InsertOrder.xls")
'Set the pointer to the worksheetSet wsheet = xlapp.worksheets(1)
'Get rowcount
rowcount = wsheet.usedrange.rows.count
For i = 1 to rowcount-1
Window("Flight Reservation").Activate
Window("Flight Reservation").WinButton("New").Click
Window("Flight Reservation").WinObject("Date of Flight:").Type wsheet.cells(i+1,1).Value
Window("Flight Reservation").WinComboBox("Fly From:").Select wsheet.cells(i+1,2).Value
Window("Flight Reservation").WinComboBox("Fly To:").Select wsheet.cells(i+1,3).Value
Window("Flight Reservation").WinButton("FLIGHT").Click
Window("Flight Reservation").Dialog("Flights Table").WinButton("OK").Click
Window("Flight Reservation").WinEdit("Name:").Set wsheet.cells(i+1,4).Value
Window("Flight Reservation").WinButton("Insert Order").ClickWait 8
Orderno = Window("Flight Reservation").WinEdit("Order No:").GetRoProperty("text")
wsheet.cells(i+1,5).Value = Orderno
If Orderno = null Then
wsheet.cells(i+1,6).Value = "Fail"
wsheet.cells(i+1,6).Font.Colorindex = 3
Else
wsheet.cells(i+1,6).Value = "Pass"
wsheet.cells(i+1,6).font.colorindex = 10End If
Next
wbook.save
wbook.close
xlapp.quit
Set wsheet = nothing
Set wbook = nothing
Set xlapp = nothing
Set linkobj = Description.Create
linkobj("micclass").Value = "Link"
Set link_col = Browser("Internet Explorer Enhanced").Page("Internet Explorer Enhanced").ChildObjects (linkobj)
count = link_col.count
msgbox count
Set xlobj = CreateObject("Excel.Application")
xlobj.Visible = true
xlobj.Workbooks.Add
For i=0 to count-1
xlobj.Cells(i,"A")= link_col(i).GetROProperty("name")
xlobj.Cells(i,"B")= link_col(i).GetROProperty("url")
Next
xlobj.ActiveWorkbook.SaveAs "c:\testdata.xl"
xlobj.Quit
Set xlobj=nothing
************************************************************
Excel Application Operations
------------------------------------
'Create Excel Application Object
Dim objExcel
Set objExcel = CreateObject("Excel.application")
---------------------------------------
'Create an excel file
Dim objExcel
Set objExcel = CreateObject("Excel.application")
objExcel.Visible = True 'To view the Operations
objExcel.Workbooks.Add 'To create new workbook /file
objExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\Administrator\Desktop\Vbs.xls"
objExcel.Quit 'To close Excel application
Set objExcel = Nothing
--------------------------------------------------------------------------------------
'Check the Existance of the File, if not exist then Create the excel file
Dim objExcel, objFso
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.application")
If Not objFso.FileExists("C:\Documents and Settings\Administrator\Desktop\Vbs.xls") Then
objExcel.Workbooks.Add
objExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\Administrator\Desktop\Vbs.xls"
End If
objExcel.Quit
Set objExcel = Nothing
Set objFso = Nothing
----------------------------------------------------------------------------------------------------
'Check the Existance of the File if exists open the file and write some data, if not exist then Create the excel file and write some data
Dim objExcel, objFso, FilePath
FilePath = "C:\Documents and Settings\Administrator\Desktop\Vbs.xls"
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.application")
If objFso.FileExists(FilePath) Then
objExcel.Workbooks.Open (FilePath)
objExcel.Worksheets(1).Cells(1,1)="QTP"
objExcel.ActiveWorkbook.Save
Else
objExcel.Workbooks.Add
objExcel.Worksheets(1).Cells(1,1)="QTP"
objExcel.ActiveWorkbook.SaveAs FilePath
End If
objExcel.Quit
Set objExcel = Nothing
Set objFso = Nothing
---------------------------------------------------------------------------------
'Check the Existance of the File if exists open the file and write some data, if not exist then Create the excel file and write some data
Dim objExcel, objFso, FilePath, objWorkbook, objWorksheet
FilePath = "C:\Documents and Settings\Administrator\Desktop\Vbs.xls"
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.application")
If objFso.FileExists(FilePath) Then
Set objWorkbook = objExcel.Workbooks.Open (FilePath)
Set objWorksheet = objWorkbook.Worksheets(2)
objWorksheet.Cells(2,2)="VB script"
objWorkbook.Save
Else
objExcel.Workbooks.Add
objExcel.Worksheets(2).Cells(2,2)="VB script"
objExcel.ActiveWorkbook.SaveAs FilePath
End If
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook=Nothing
Set objExcel = Nothing
Set objFso = Nothing
------------------------------------------------------------------------------------------------------
'Read test data from an excel file and perform Data driven Testing for Login Operation
Dim objExcel, objWorkbook, objWorksheet, row_Count
'Create Excel Application Object, used to perform oprations on Excel Application
Set objExcel = CreateObject("Excel.Application")
'Create Excel WorkBook Object, used to perform oprations on Excel Work books /files
Set objWorkbook = objExcel.Workbooks.Open ("C:\Documents and Settings\Administrator\Desktop\input.xls")
'Create Excel WorkSheet Object, used to perform oprations on Excel Sheets
Set objWorksheet = objWorkbook.Worksheets("Sheet1")
row_Count = objWorksheet.usedrange.rows.count
For i = 2 to row_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")
wait 1
Dialog("Login").WinButton("OK").Click
Window("Flight Reservation").Close
Next
objWorkbook.Close
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
-----------------------------------------------------------------------------------------------
'Read test data from an excel file and perform Data driven Testing for Login Operation
'Export Results to the Same file
Dim objExcel, objWorkbook, objWorksheet, row_Count
'Create Excel Application Object, used to perform oprations on Excel Application
Set objExcel = CreateObject("Excel.Application")
'Create Excel WorkBook Object, used to perform oprations on Excel Work books /files
Set objWorkbook = objExcel.Workbooks.Open ("C:\Documents and Settings\Administrator\Desktop\input.xls")
'Create Excel WorkSheet Object, used to perform oprations on Excel Sheets
Set objWorksheet = objWorkbook.Worksheets("Sheet1")
row_Count = objWorksheet.usedrange.rows.count
objWorksheet.Cells(1,3)="Test Result"
For i = 2 to row_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")
wait 1
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"
SystemUtil.CloseDescendentProcesses
End If
Next
objWorkbook.Save
objWorkbook.Close
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
------------------------------------------------------------------------------------------------------------
'Read test data from an excel file and perform Data driven Testing for Login Operation
'Export Results and Error messages to the Same file
Dim objExcel, objWorkbook, objWorksheet, row_Count
'Create Excel Application Object, used to perform oprations on Excel Application
Set objExcel = CreateObject("Excel.Application")
'Create Excel WorkBook Object, used to perform oprations on Excel Work books /files
Set objWorkbook = objExcel.Workbooks.Open ("C:\Documents and Settings\Administrator\Desktop\input.xls")
'Create Excel WorkSheet Object, used to perform oprations on Excel Sheets
Set objWorksheet = objWorkbook.Worksheets("Sheet1")
row_Count = objWorksheet.usedrange.rows.count
objWorksheet.Cells(1,3)="Test Result"
objWorksheet.Cells(1,4)="Error Message"
For i = 2 to row_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")
wait 1
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,4) = Dialog("Login").Dialog("Flight Reservations").Static("Agent name must be at").GetROProperty("text")
objWorksheet.Cells(i, 3)="Login Failed"
SystemUtil.CloseDescendentProcesses
End If
Next
objWorkbook.Save
objWorkbook.Close
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
------------------------------------------------------------------------------------------------------
====================
'Export Links from Google Page to Excel workbook 2nd sheet
Dim objExcel, objWorkbook,objWorksheet
Dim oLink, Links, i, filepath
filepath="C:\Documents and Settings\Administrator\Desktop\input.xls"
Set objExcel=createobject("Excel.Application")
Set objWorkbook=objExcel.Workbooks.Open(filepath)
Set objWorksheet=objWorkbook.Worksheets("Sheet2")
Set oLink=description.Create
oLink("micclass").value="Link"
Set Links=Browser("title:=Google").page("title:=Google").ChildObjects(oLink)
objWorksheet.cells(1,"A")="Link Names"
For i=0 to Links.count-1 Step 1
objWorksheet.cells(i+2,"A")=Links(i).getroproperty("text")
Next
objWorkbook.Save
objWorkbook.Close
objExcel.Quit
Set objWorksheet=Nothing
Set objWorkbook=Nothing
Set objExcel=Nothing
------------------------------------------------------------------------------------------------------
''Read button Names from Login dialog and compare with expected values in Excel sheet (One to One exact match)
Dim objExcel, objWorkbook,objWorksheet
Dim oButton, Buttons, i, filepath
filepath="C:\Documents and Settings\Administrator\Desktop\input.xls"
Set objExcel=createobject("Excel.Application")
Set objWorkbook=objExcel.Workbooks.Open(filepath)
Set objWorksheet=objWorkbook.Worksheets("Sheet3")
Set oButton=description.Create
oButton("micclass").value="WinButton"
Set Buttons=Dialog("text:=Login").ChildObjects(oButton)
objWorksheet.cells(1,"B")="Actual"
objWorksheet.cells(1,"C")="Results"
For i=0 to Buttons.count-1 Step 1
objWorksheet.cells(i+2,"B")=Buttons(i).Getroproperty("text")
Next
objWorkbook.Save
Rows_Count=objWorksheet.usedrange.rows.count
For j= 2 to Rows_Count Step 1
Expected=objWorksheet.cells(j,"A")
Actual = objWorksheet.cells(j, "B")
If Expected = Actual Then
objWorksheet.cells(j,"C")= "Passed"
Else
objWorksheet.cells(j,"C")= "Failed"
End If
Next
objWorkbook.Save
objWorkbook.Close
objExcel.Quit
Set objWorksheet=Nothing
Set objWorkbook=Nothing
Set objExcel=Nothing
--------------------------------------------------------------------------------
''Read button Names from Login dialog and compare with expected values in Excel sheet (One to One Texual Comparision)
Dim objExcel, objWorkbook,objWorksheet
Dim oButton, Buttons, i, filepath
filepath="C:\Documents and Settings\Administrator\Desktop\input.xls"
Set objExcel=createobject("Excel.Application")
Set objWorkbook=objExcel.Workbooks.Open(filepath)
Set objWorksheet=objWorkbook.Worksheets("Sheet3")
Set oButton=description.Create
oButton("micclass").value="WinButton"
Set Buttons=Dialog("text:=Login").ChildObjects(oButton)
objWorksheet.cells(1,"B")="Actual"
objWorksheet.cells(1,"C")="Results"
For i=0 to Buttons.count-1 Step 1
objWorksheet.cells(i+2,"B")=Buttons(i).Getroproperty("text")
Next
objWorkbook.Save
Rows_Count=objWorksheet.usedrange.rows.count
For j= 2 to Rows_Count Step 1
Expected=objWorksheet.cells(j,"A")
Actual = objWorksheet.cells(j, "B")
If StrComp (Expected, Actual, 1) = 0 Then
objWorksheet.cells(j,"C")= "Passed"
Else
objWorksheet.cells(j,"C")= "Failed"
End If
Next
objWorkbook.Save
objWorkbook.Close
objExcel.Quit
Set objWorksheet=Nothing
Set objWorkbook=Nothing
Set objExcel=Nothing
--------------------------------------------------------------------------------
''Read button Names from Login dialog and compare with expected values in Excel sheet (Many to Many Texual Comparision)
Dim objExcel, objWorkbook,objWorksheet
Dim oButton, Buttons, i, filepath
filepath="C:\Documents and Settings\Administrator\Desktop\input.xls"
Set objExcel=createobject("Excel.Application")
Set objWorkbook=objExcel.Workbooks.Open(filepath)
Set objWorksheet=objWorkbook.Worksheets("Sheet3")
Set oButton=description.Create
oButton("micclass").value="WinButton"
Set Buttons=Dialog("text:=Login").ChildObjects(oButton)
objWorksheet.cells(1,"B")="Actual"
objWorksheet.cells(1,"C")="Results"
For i=0 to Buttons.count-1 Step 1
objWorksheet.cells(i+2,"B")=Buttons(i).Getroproperty("text")
Next
objWorkbook.Save
Rows_Count=objWorksheet.usedrange.rows.count
For j= 2 to Rows_Count Step 1
Expected=objWorksheet.cells(j,"A")
For k= 2 to Rows_Count Step 1
Actual = objWorksheet.cells(k, "B")
Flag=0
If StrComp(Expected, Actual, 1)=0 Then
Flag=1
Exit For
Else
Flag=0
End If
Next
If Flag = 1 Then
objWorksheet.cells(k, "C") = "Passed"
Else
objWorksheet.cells(k, "C") = "Failed"
End If
Next
objWorkbook.Save
objWorkbook.Close
objExcel.Quit
Set objWorksheet=Nothing
Set objWorkbook=Nothing
Set objExcel=Nothing
---------------------------------------------------------------------------------------------------------
'Create an excel workbook / file and rename 1st sheet as "Module", 2nd sheet as "TestCase", and 3rd sheet as "Test Step
Dim objExcel
Set objExcel = Createobject("Excel.Application")
objExcel.Workbooks.Add
objExcel.Visible = True
objExcel.Worksheets(1).Name ="Module"
wait 2
objExcel.Worksheets(2).Name ="TestCase"
wait 2
objExcel.Worksheets(3).Name ="TestStep"
objExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\Administrator\Desktop\dec.xls"
objExcel.Quit
Set objExcel = Nothing
--------------------------------------------------------------------------------
'Create an excel workbook / file and Add one more sheet
Dim objExcel
Set objExcel = Createobject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
Wait 2
objExcel.Worksheets.Add
objExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\Administrator\Desktop\dec.xls"
objExcel.Quit
Set objExcel = Nothing
-------------------------
Search for a particular value in Excel
Set appExcel = CreateObject(“Excel.Application”)
appExcel.visible=true
Set objWorkBook = appExcel.Workbooks.Open (filepath)’opens the sheet
Set objSheet = appExcel.Sheets(“Sheet1?)’ To select particular sheet
With objSheet.UsedRange ‘ select the used range in particular sheet
Set c = .Find (“nn”)’ data to find
For each c in objSheet.UsedRange’ Loop through the used range
If c=”nn” then’ compare with the expected data
c.Interior.ColorIndex = 40' make the gary color if it finds the data
End If
Set c = .FindNext(c)’ next search
next
End With
objWorkBook.save
objWorkBook.close
set appExcel=nothing
Author:Mohan Kakarla
Q2. Write a script in QTP to import data from an external data sheet using Excel application File System Object?
'Script to insert an order and print the Order Number
'Create an object to interact with Excel Library
Set xlapp = CreateObject("Excel.Application")
'Open the excel workbook
Set wbook = xlapp.workbooks.Open("E:\InsertOrder.xls")
'Set the pointer to the worksheetSet wsheet = xlapp.worksheets(1)
'Get rowcount
rowcount = wsheet.usedrange.rows.count
For i = 1 to rowcount-1
Window("Flight Reservation").Activate
Window("Flight Reservation").WinButton("New").Click
Window("Flight Reservation").WinObject("Date of Flight:").Type wsheet.cells(i+1,1).Value
Window("Flight Reservation").WinComboBox("Fly From:").Select wsheet.cells(i+1,2).Value
Window("Flight Reservation").WinComboBox("Fly To:").Select wsheet.cells(i+1,3).Value
Window("Flight Reservation").WinButton("FLIGHT").Click
Window("Flight Reservation").Dialog("Flights Table").WinButton("OK").Click
Window("Flight Reservation").WinEdit("Name:").Set wsheet.cells(i+1,4).Value
Window("Flight Reservation").WinButton("Insert Order").ClickWait 8
Orderno = Window("Flight Reservation").WinEdit("Order No:").GetRoProperty("text")
wsheet.cells(i+1,5).Value = Orderno
If Orderno = null Then
wsheet.cells(i+1,6).Value = "Fail"
wsheet.cells(i+1,6).Font.Colorindex = 3
Else
wsheet.cells(i+1,6).Value = "Pass"
wsheet.cells(i+1,6).font.colorindex = 10End If
Next
wbook.save
wbook.close
xlapp.quit
Set wsheet = nothing
Set wbook = nothing
Set xlapp = nothing
No comments
Post a Comment