VB / QTP scripts with examples

VBScript Program to reverse a string without using strreverse

'Program to reverse a string without using strreverse

MyStr = inputbox("Enter the String:")
nCnt = Len(MyStr)

For i = 1 to nCnt

   RevStr = Mid(MyStr,i,1)&RevStr

Next

Msgbox RevStr

QTP code to terminate all instances of Internet  explorer using collections


'Code to close all Internet explorer browser process using QTP

Dim oWMIService, allIExplorer,iEItem
Dim strComputer

strcomputer="."

'Get the WMI object
Set oWMIService=GetObject("Winmgmts:\\"& strcomputer & "\root\cimv2")

'Get collection of processes for with name iexplore.exe
set allIExplorer=oWMIService.execquery("Select * from win32_process where name='iexplore.exe'")
name
'Loop through each process and terminate it
For each iEItem in allIExplorer
iEItem.terminate
Next

Set allIExplorer= Nothing
Set oWMIService= Nothing


QTP script to send an email from outlook

'Call to the function for sending the mail on mail id 'abc@abc.com'
Call SendMail("abc@abc.com","Hi","Hi","")

' THis function will create an email and send it using outlook

Function SendMail(SendTo, Subject, Body, Attachment)

'CreateObject method to create an Outlook Application object
Set ol=CreateObject("Outlook.Application")

'Create a new outlook mail object
Set Mail=ol.CreateItem(0)

'Add the email address to the recipient list of the message
Mail.to=SendTo

'Add the subject of the message
Mail.Subject=Subject

'Add the body of the mail message
Mail.Body=Body

'Add the attachment to the mail message
If (Attachment <> "") Then
Mail.Attachments.Add(Attachment)
End If

'Send the mail message
Mail.Send

'Free up the memory
ol.Quit
Set Mail = Nothing
Set ol = Nothing

End Function

public void ConnectToDatabase()
{

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

Set objConn=CreateObject("ADODB.Connection")
Set objRecordset=CreateObject("ADODB.Recordset")

objConn.Open "DRIVER={Oracle in OraHome92};SERVER={Servername};UID={Username};PWD={Password};DBQ={Dbnmae}"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "Select {Col name} from tablename",objConn,adOpenStatic,adLockOptimistic

While not (objRecordset.EOF)

   objRecordset.MoveNext
   Msgbox "Result" & objRecordset.Fields.Item("{Col name}")& vbCrLf 

Wend

objRecordset.Close
objConn.Close

Set objRecordset=Nothing
Set objConn=Nothing

}


GetTOproperties, SetTOproperty, GetTOproperty & GetROproperty - methods explained with sample scripts.



Before we get started, I would like to take some time explaining about the TO, RO, Get and Set part of the methods.



TO : Stands for Test Object (The object that we have recorded in our object repository)

RO : Stands for Runt-time Object (The application object we are testing against the object in the Object repository)

Get : Is used to get (return) the current property/ies of a Run-time or a test object.

Set : Is used to set (return) the property of a test object.



GetTOproperties:

This method is used to return get the properties and their corresponding values from the recorded object in our object repository.



The following example explains the usage of GetTOproperties method:
'################################
'### Get TO Properties Script ###
'################################

systemutil.Run "iexplore.exe", "http://www.google.com"

Set oWebEdit=Browser("Google").Page("Google").WebEdit("q") 'Create webedit object

Set TOProperties=oWebEdit.GetTOProperties 'Get TO Properties

For i = 0 to TOProperties.count - 1 'Looping through all the properties
    sName=TOProperties(i).Name ' Name of the property
    sValue=TOProperties(i).Value 'Value of the property
    isRegularExpression=TOProperties(i).RegularExpression 'Is it a Regular expression
    Msgbox sName & " -> " & sValue & "->" & isRegularExpression 
Next

'##############
'### Result:###
'##############
' type -> ext -> true
' name -> q -> true
'### End of Script ####





SetTOproperty :



This method is used to set the value of a particular property of an object in the object repository.



GetTOproperty:



This method is used to get the current value of a property of an object in the object repository. If we had used the SetTOproperty method to change the existing property of an object in the OR, GetTOproperty method would only retrieve the current value of the property (ie, what ever value was set using SetTOproperty).



Note : Whatever value we set during the script execution is not retained after the execution is complete. The values in the OR automatically gets back to what it was at the beginning of the execution just like the run-time data tables.
'#############################################
'### Set TO Property & Get TO property ###
'#############################################

oldName = oWebEdit.GetTOProperty("name")
msgbox "Old Name ->" & oldName
oWebEdit.SetTOProperty "name","New Value"
NewName = oWebEdit.GetTOProperty("name")
msgbox "New Name ->" & NewName

'##############
'### Result:###
'##############
' Old Name ->q
' New Name ->New Value
'### End of Script ###



GetROProperty:

This method is used to get the runtime value of a property in an application object.



Note : SetROproperty method is not supported by QTP, hence it is unavailable.


'#######################
'### Get RO Property ###
'#######################

oWebEdit=Browser("Google").Page("Google").WebEdit("q").GetROProperty("value") 
msgbox oWebEdit

'###############
'### Result: ###
'###############

'### The text in the webedit control is displayed
'### End of Script ###

QTP VB Script Functions

Llist of all VBScript built in functions.

Abs Function: Returns the absolute value of a number.
Array Function: Returns a Variant containing an array.
Asc Function: Returns the ANSI character code corresponding to the first letter in a string.
Atn Function: Returns the arctangent of a number.
CBool Function: Returns an expression that has been converted to a Variant of subtype Boolean.
CByte Function: Returns an expression that has been converted to a Variant of subtype Byte.
CCur Function: Returns an expression that has been converted to a Variant of subtype Currency.
CDate Function: Returns an expression that has been converted to a Variant of subtype Date.
CDbl Function: Returns an expression that has been converted to a Variant of subtype Double.
Chr Function: Returns the character associated with the specified ANSI character code.
CInt Function: Returns an expression that has been converted to a Variant of subtype Integer.
CLng Function: Returns an expression that has been converted to a Variant of subtype Long.
Cos Function: Returns the cosine of an angle.
CreateObject Function: Creates and returns a reference to an Automation object.
CSng Function: Returns an expression that has been converted to a Variant of subtype Single.
CStr Function: Returns an expression that has been converted to a Variant of subtype String.
Date Function: Returns the current system date.
DateAdd Function: Returns a date to which a specified time interval has been added.
DateDiff Function: Returns the number of intervals between two dates.
DatePart Function: Returns the specified part of a given date.
DateSerial Function: Returns a Variant of subtype Date for a specified year, month, and day.
DateValue Function: Returns a Variant of subtype Date.
Day Function: Returns a whole number between 1 and 31, inclusive, representing the day of the month.
Eval Function: Evaluates an expression and returns the result.
Exp Function: Returns e (the base of natural logarithms) raised to a power.
Filter Function: Returns a zero-based array containing subset of a string array based on a specified filter criteria.
Fix Function: Returns the integer portion of a number.
FormatCurrency Function: Returns an expression formatted as a currency value using the currency symbol defined in the system control panel.
FormatDateTime Function: Returns an expression formatted as a date or time.
FormatNumber Function: Returns an expression formatted as a number.
FormatPercent Function: Returns an expression formatted as a percentage (multiplied by 100) with a trailing % character.
GetLocale Function: Returns the current locale ID value.
GetObject Function: Returns a reference to an Automation object from a file.
GetRef Function: Returns a reference to a procedure that can be bound to an event.
Hex Function: Returns a string representing the hexadecimal value of a number.
Hour Function: Returns a whole number between 0 and 23, inclusive, representing the hour of the day.
InputBox Function: Displays a prompt in a dialog box, waits for the user to input text or click a button, and returns the contents of the text box.
InStr Function: Returns the position of the first occurrence of one string within another.
InStrRev Function: Returns the position of an occurrence of one string within another, from the end of string.
Int Function: Returns the integer portion of a number.
IsArray Function: Returns a Boolean value indicating whether a variable is an array.
IsDate Function: Returns a Boolean value indicating whether an expression can be converted to a date.
IsEmpty Function: Returns a Boolean value indicating whether a variable has been initialized.
IsNull Function: Returns a Boolean value that indicates whether an expression contains no valid data (Null).
IsNumeric Function: Returns a Boolean value indicating whether an expression can be evaluated as a number.
IsObject Function: Returns a Boolean value indicating whether an expression references a valid Automation object.
Join Function: Returns a string created by joining a number of substrings contained in an array.
LBound Function: Returns the smallest available subscript for the indicated dimension of an array.
LCase Function: Returns a string that has been converted to lowercase.
Left Function: Returns a specified number of characters from the left side of a string.
Len Function: Returns the number of characters in a string or the number of bytes required to store a variable.
LoadPicture Function: Returns a picture object. Available only on 32-bit platforms.
Log Function: Returns the natural logarithm of a number.
LTrim Function: Returns a copy of a string without leading spaces.
Mid Function: Returns a specified number of characters from a string.
Minute Function: Returns a whole number between 0 and 59, inclusive, representing the minute of the hour.
Month Function: Returns a whole number between 1 and 12, inclusive, representing the month of the year.
MonthName Function: Returns a string indicating the specified month.
MsgBox Function: Displays a message in a dialog box, waits for the user to click a button, and returns a value indicating which button the user clicked.
Now Function: Returns the current date and time according to the setting of your computer's system date and time.
Oct Function: Returns a string representing the octal value of a number.
Replace Function: Returns a string in which a specified substring has been replaced with another substring a specified number of times.
RGB Function: Returns a whole number representing an RGB color value.
Right Function: Returns a specified number of characters from the right side of a string.
Rnd Function: Returns a random number.
Round Function: Returns a number rounded to a specified number of decimal places.
RTrim Function: Returns a copy of a string without trailing spaces.

UFT/QTP Excel Automation

Excel Automation Object Model

Microsoft has developed the Excel application with heirarachy of object model.We can do excel operations using excel object model.

Simple object model Example: Excel Application --> Workbooks--> Worksheet--> cells

=> Create an Excel File:

'Create a new Microsoft Excel object

Set myxl = createobject("excel.application")

'To make Excel visible

myxl.Application.Visible = true 

  

myxl.Workbooks.Add

wait 2

'Save the Excel file as qtp.xls

myxl.ActiveWorkbook.SaveAs  "D:\qtp.xls"

     

'close Excel

myxl.Application.Quit

Set myxl=nothing

=>Create an Excel File , Enter some data , Save the Excel and close the Excel: 

Set myxl = createobject("excel.application")

'Make sure that you have created an excel file before exeuting the script.

'Use the path of excel file in the below code

'Also make sure that your excel file is in Closed state before exeuting the script.

myxl.Workbooks.Open "D:\qtp.xls" 

myxl.Application.Visible = true

'this is the name of  Sheet  in Excel file "qtp.xls"   where data needs to be entered 

set mysheet = myxl.ActiveWorkbook.Worksheets("Sheet1")

'Enter values in Sheet1.

'The format of entering values in Excel is excelSheet.Cells(row,column)=value

mysheet.cells(1,1).value ="Name"

mysheet.cells(1,2).value ="Age"

mysheet.cells(2,1).value ="Ram"

mysheet.cells(2,2).value ="20"

mysheet.cells(3,1).value ="Raghu"

mysheet.cells(3,2).value ="15"

'Save the Workbook

=>Read the data from Excel File:

Set myxl = createobject("excel.application")

'Make sure that you have created an excel file before exeuting the script. 

'Use the path of excel file in the below code

'Also make sure that your excel file is in Closed state

myxl.Workbooks.Open "D:\qtp.xls"

myxl.Application.Visible = true

'this is the name of  Sheet  in Excel file "qtp.xls"   where data needs to be entered 

set mysheet = myxl.ActiveWorkbook.Worksheets("Sheet1")

'Get the max row occupied in the excel file 

Row=mysheet.UsedRange.Rows.Count

'Get the max column occupied in the excel file 

Col=mysheet.UsedRange.columns.count

'To read the data from the entire Excel file

For  i= 1 to Row

    For j=1 to Col

        Msgbox  mysheet.cells(i,j).value

    Next

Next

'Save the Workbook

myxl.ActiveWorkbook.Save

'Close the Workbook

myxl.ActiveWorkbook.Close

'Close Excel

myxl.Application.Quit

Set mysheet =nothing

Set myxl = nothing

?Compare Two Excel sheets Cell by cell:

Mismatch=0

Set myxl = createobject("excel.application")

'To make Excel visible

myxl.Visible = True

'Open  a workbook "qtp1.xls"

Set Workbook1= myxl.Workbooks.Open("C:\qtp1.xls")

'Open  a workbook "qtp2.xls"

Set Workbook2= myxl.Workbooks.Open("C:\qtp2.xls")

Set  mysheet1=Workbook1.Worksheets("Sheet1")

Set  mysheet2=Workbook2.Worksheets("Sheet1")

'Compare two sheets cell by cell

For Each cell In mysheet1.UsedRange

'Highlights the cell if  cell values not match

       If cell.Value <>mysheet2.Range(cell.Address).Value Then

           'Highlights the cell if  cell values not match

           cell.Interior.ColorIndex = 3

              mismatch=1

       End If

   Next

If Mismatch=0 Then

    Msgbox "No Mismach exists"

End If

'close the workbooks

Workbook1.close

Workbook2.close

myxl.Quit

set myxl=nothing

=>Search for Particular value in Excel:

Set myxl = createobject("excel.application")

'Make sure that you have created an excel file before exeuting the script.

'Use the path of excel file in the below code

'Also make sure that your excel file is in Closed state before executing the script.

myxl.Workbooks.Open "D:\qtp.xls"

myxl.Application.Visible = true

'This is the name of  Sheet  in Excel file "qtp.xls"   where data needs to be entered 

set mysheet = myxl.ActiveWorkbook.Worksheets("Sheet1")

'Contents of Sheet1

'Name        Age

'Ram         20

'Raghu       15

'Select the used range in particular sheet

With mysheet.UsedRange  

' Data "Ram" to search

' Loop through the used range

            For each search_data in mysheet.UsedRange

            ' compare with the expected data

                         If search_data="Ram" then  

                        'make the cell with color  if it finds the data

                                    search_data.Interior.ColorIndex = 40 

                        End If

           next

End With

'Save the Workbook

myxl.ActiveWorkbook.Save

'Close the Workbook

myxl.ActiveWorkbook.Close

'Close Excel

myxl.Application.Quit

Set mysheet =nothing

Set myxl = nothing

?Copy an Excel sheet to another Excel sheet:

Set myxl = createobject("excel.application")

'To make Excel visible

myxl.Visible = True

'Open  a workbook "qtp1.xls"

Set Workbook1= myxl.Workbooks.Open("C:\qtp1.xls")

'Open  a workbook "qtp2.xls"

Set Workbook2= myxl.Workbooks.Open("C:\qtp2.xls")

'Copy  the used range of  workbook "qtp1.xls"

Workbook1.Worksheets("Sheet1").UsedRange.Copy

'Paste the copied values in above step in the  A1 cell  of  workbook "qtp2.xls"

Workbook2.Worksheets("Sheet1").Range("A1").PasteSpecial Paste =xlValues

'Save the workbooks

Workbook1.save

Workbook2.save

'close the workbooks

Workbook1.close

Workbook2.close

myxl.Quit

set myxl=nothing

=> Addsheet Method:

Description: Adds the specified sheet to the run-time Data Table and returns the sheet so that you can directly set properties of the new sheet in the same statement.

Syntax: DataTable.AddSheet(SheetName)

'Create a datatable sheet during Run time.This sheet will be available during run time only. 

'We can view  this sheet  in Result Summaryunder section "Run Time data Table".

datatable.AddSheet("Qtpworld")

'To add column name and  a default value under them.

datatable.GetSheet("Qtpworld").AddParameter "name","Ram"

datatable.GetSheet("Qtpworld").AddParameter "age","18"

wait 5

=> DeleteSheet Method:

Description: Deletes the specified sheet from the run-time Data Table.

Syntax: DataTable.DeleteSheet SheetID

Example:

'Create a datatable sheet during Run time.This sheet will be available during run time only. 

'We can view  this sheet  in Result Summary  under section "Run Time data Table"  .

datatable.AddSheet("Qtpworld")

'To delete   datatable sheet

datatable.DeleteSheet("Qtpworld")

datatable.DeleteSheet("Global")

wait 3

=> Import Method:

Description: Imports the specified Microsoft Excel file to the run-time Data Table.

Syntax: DataTable.Import(FileName)

=> Export Method:

Description: Saves a copy of the run-time Data Table in the specified location.

Syntax: DataTable.Export(FileName)

Example:

'If data is stored in multiple  sheet  in  external Excel  Workbook, 

'we can import  multiple sheet  data into Datatable and  

'Do neccessary operation on the imported data.

datatable.Import  "C:\qtptest.xls"

'To get the total count of QTP datatable sheets

msgbox datatable.GetSheetCount

'After the operations are done,you can export the all the qtp datasheets to the External file

'Create a datatable sheet during Run time.This sheet will be available during  run time only. 

datatable.Export  "C:\qtptest.xls"

'We can view  this sheet  in Result Summary  under section "Run Time data Table"  .

datatable.AddSheet("Qtpworld")

'To delete   datatable sheet

datatable.DeleteSheet("Qtpworld")

datatable.DeleteSheet("Global")

wait 3

=> ImportSheet Method:

Description: Imports a sheet of a specified file to a specified sheet in the run-time Data Table. The data in the imported sheet replaces the data in the destination sheet (see SheetDest argument).

Syntax: DataTable.ImportSheet(FileName, SheetSource, SheetDest)

=>ExportSheet Method:

Description: Exports a specified sheet of the run-time Data Table to the specified file.

If the specified file does not exist, a new file is created and the specified sheet is saved.If the current file exists, but the file does not contain a sheet with the specified sheet name, the sheet is inserted as the last sheet of the file.

If the current file exists and the file contains the specified sheet, the exported sheet overwrites the existing sheet.

Syntax: DataTable.ExportSheet(FileName, DTSheet)

Example:

'If data is stored in a particular  sheet  in  external Excel  Workbook , 

'we can import  only that particular sheet  data into Datatable and 

'do neccessary operation on the imported data.

'Create a sheet  "Sheet1"  in  qtp

datatable.AddSheet  "Sheet1"

'Sheet1 data from excel file contains the following data

'Name        Age

'Ramu        20

'Rakesh      24

'Import Sheet1 data from excel file to qtp sheet "Sheet1"

datatable.ImportSheet "C:\qtpsheet.xls","Sheet1","Sheet1"

'Add a column "Result"  for displaying result  in qtp sheet

datatable.GetSheet("Sheet1").AddParameter  "Result",""

wait 2

'Apply the logic:  if age is less than 18 then  the guy is  " Minor"   else "Major"

row =datatable.GetSheet("Sheet1").GetRowCount

For i = 1 to row

    datatable.GetSheet("Sheet1").SetCurrentRow(i)

    If   datatable.Value("Age","Sheet1") > 18  Then

        datatable.Value("Result","Sheet1") = "Major"

    Else

        datatable.Value("Result","Sheet1") = "Minor"

    End If

Next

'Export  the qtp sheet "Sheet1"  bak to external  excel  

Datatable.ExportSheet "C:\qtpsheet.xls","Sheet1"

' After exporting  you can see that the excel file now has been updated with result

 => GetSheet Method: 

Description: Returns the specified sheet from the run-time Data Table.

Syntax: DataTable.GetSheet(SheetID)

Example given below

=> GetSheetCount Method:

Description: Returns the total number of sheets in the run-time Data Table.

Syntax: DataTable.GetSheetCount

Example given below

=> GetCurrentRow Method:

Description: Returns the current (active) row in the first sheet in the run-time Data Table (global sheet).

Syntax: DataTable.GetCurrentRow

Example given below

=> GetRowCount Method:

Description: Returns the total number of rows in the longest column in the first sheet in the run-time Data Table (global sheet).

Syntax: DataTable.GetRowCount

Example given below

=> SetCurrentRow Method:

Description: Sets the specified row as the current (active) row in the run-time Data Table.

Syntax: DataTable.SetCurrentRow(RowNumber)

Example:

'Create a datatable sheet during Run time. 

'This sheet will be available during  run time only. 

'We can view  this sheet  in Result Summary under section "Run Time data Table"  .

datatable.AddSheet("Qtpworld")

'To add column name and  a default value under them.

datatable.GetSheet("Qtpworld").AddParameter "name","Ram"

datatable.GetSheet("Qtpworld").AddParameter "age","18"

'Enter data into second row of datatsheet  "Qtpworld"

datatable.GetSheet("Qtpworld").SetCurrentRow(2)

datatable.Value("name","Qtpworld")="Ramu"

datatable.Value("age","Qtpworld")="23"

'total number of  datasheets in the run-time Data Table 

Msgbox  datatable.GetSheetCount

'Get the max used range of the datasheet

row=datatable.GetSheet("Qtpworld").GetRowCount

'Loop to read all the data in the datasheet "Qtpworld"

For   Drow= 1 to row

    datatable.GetSheet("Qtpworld").SetCurrentRow(Drow)

    Msgbox   datatable.Value("name","Qtpworld")

    Msgbox   datatable.Value("age","Qtpworld")

Msgbox   "Current Row is: " & datatable.GetSheet("Qtpworld").GetCurrentRow

Next

=> GlobalSheet Property:

Description: Returns the first sheet in the run-time Data Table (global sheet).

Syntax: DataTable.GlobalSheet

Example given below

=> LocalSheet Property: 

Description: Returns the current (active) local sheet of the run-time Data Table.

Syntax: DataTable.LocalSheet

Example:

'To add column name in Global Sheet and  a default value under them.

datatable.GlobalSheet.AddParameter "name","ramu"

datatable.GlobalSheet.AddParameter "age","18"

'To add column name in Local Sheet and  a default value under them.

datatable.LocalSheet.AddParameter "name","Rakesh"

datatable.LocalSheet.AddParameter "age","22"