Example to explain working with excel application in QTP using VBScript

 In this post, we will know how to work with excel application with an example . We will explain how to copy a source file and create an destination file with same name or a dynamic name generated. We will also explain how to copy data from an excel worksheet to another worksheet, delete an excel sheet, and rename an excel worksheet using excel application object.

 The problem we will resolve using this code is as follows. 

In the below code, we will explain how to implement the above using vb script.

destfile = CopyFile("D:\testt\TestSupport_1.xls", "D:\testt\", 1)

copydata "D:\testt\generatedRpt.xls", destfile, "general_report", "temp"

masterfile = Copyfile(destfile, "D:\testt\TestingSupport_Daily_Data.xls",0)

'' Description - This function will copy the file from source location to destination folder. In this example we have used below three parameter as per our need.

''Source file - Exact name and location of the source file. This is an external file generated at a fixed location which was first part of problem and will be handled using QTP

''DestinationFolder - this is either the folder location with or without file name. 

''filename - this shows filename is included in the the destinationfolder. In case of dynamic naming of the file, provide value as 1 else provide value as 0.

function CopyFile(SourceFile, DestinationFolder, filename)

if (filename =1) then

'' generate a dynamic name for the file in case filename argument is provided as 1

    DestinationFile = DestinationFolder + "TestSupport" + cstr(DatePart("m",Now()))+ cstr(DatePart("d",Now()))+ cstr(DatePart("yyyy",Now())) + ".xls"

else

   DestinationFile = Destinationfolder

end If

    Set fso = CreateObject("Scripting.FileSystemObject")

    'Check to see if the file already exists in the destination folder

    If fso.FileExists(DestinationFile) Then

    ''In case of destinationfile already exists, delete the existing file

fso.DeleteFile DestinationFile, True

    End If

'' Copy the file from source location to destination location

    fso.CopyFile SourceFile, DestinationFile, True

''close the instance of filesystem object

    Set fso = Nothing

''return the value of file name as the rsturn value of the function.

copyfile = Destinationfile

End function

'

'' Description - this function will copy data from sheet1 of workbook1 to sheet2 of workbook2.

sub copydata(workbook1, workbook2, sheet1,sheet2)

'' Create an instance of excel object

Set objExcel = CreateObject("Excel.Application") 

'' define if the excel needs to be displayed and alerts to be displayed. here the excel will be visible but no alerts will be displayed in case of alert prompt in the excel application.

objExcel.Visible = True

objExcel.DisplayAlerts = False

'' create an instance of excel workbooks for source as well as destination workbook

Set objsrcFile= objExcel.Workbooks.Open(workbook1)   

Set objdestfile = objExcel.Workbooks.Open(workbook2) 

objsrcfile.Activate

'' create instance of worksheet in the workbook

Set objsrcSheet = objsrcfile.Worksheets(sheet1)

Set objdestSheet = objdestfile.Worksheets(sheet2)

'' gather the data from the source file to destination file

'' In the below example , we are copying the data from 4th row in source file to destination file. we can start from first row also if required.

icolcount = objsrcSheet.usedrange.columns.count

irowcount = objsrcSheet.usedrange.rows.count

for i = 4 to irowcount-1

for j = 1 to icolcount-1

''copies data from the destination file to the source file

objdestSheet.cells(i-3,j)= objsrcsheet.cells(i,j)

next

next

objsrcfile.save

objdestfile.save

''This is again a customised code to delete a sheet from excel file. 

objdestfile.sheets("Previous Day").Delete

'' This step renames the sheet in the destinstion file.

Set objWrksheet = objdestfile.Worksheets("Today")

objWrksheet.Name = "Previous Day"

set objWrkSheet = nothing

''Rename another sheet in the workbook

Set objWrksheet = objdestfile.Worksheets("temp")

objWrkSheet.Name = "Today"

set objWrkSheet = nothing

''Add a temp table at the end of sheets and name it as temp

Set objWrkSheet = objdestfile.Sheets.Add(, objdestfile.Sheets(objdestfile.Sheets.Count))

objWrkSheet.Name = "temp"

''Save the excel file and close the instance of excel aplication

objdestfile.save

objsrcfile.close

objdestfile.close

objexcel.quit

set objexcel = nothing

msgbox "done"

end sub

Using Parameterization for data driven testing in QTP/ UFT

UFT/ QTP enables us to configure the values for properties and other items by defining a value as a constant or a parameter. We can also use regular expressions for some values to increase the flexibility and adaptability of our tests. A parameter is value that is defined or generated externally and is retrieved during a run session. A parameter for example can take value from an external source like excel sheet or UFT data tables.

The major advantage or need of using parameters in test is to create data driven tests. 

Let me explain this with an example. Suppose there are two edit controls in the Page Username and Password and user clicks on login button to login.

Suppose the requirement is to create test which will validate different users are able to login into application successfully. If we use constant value for Username and Password and have to validate for 100 Users, we will need to create 100 tests, Maintaining 100 tests is a tedious task and time consuming activity. 

Let me explain this with an example, suppose there is change in identification property of the Username edit box. E.g. Name of Username field is changed to E-Mail and the user using constants is using the local object repository for each test. We need to make change in each of the tests. Maintenance is also difficult because of rework for minor change in script. Also keeping the test executor understand the difference between the tests and which test is for which purpose is difficult task with growth in size of test suite.

Using Parameters in the test solves the above problem, we can iterate the same test steps execution using multiple data set and can compare the expected result with the pre-defined data stored in the data source. 

For e.g for successful login or incorrect password, parameterization using data table object, the code is like:

Parameterization

Browser("qaautomationqtp").page("qaautomationqtp").WebEdit("UserName").Set DataTable("UserName", dtGlobalSheet)

Browser("qaautomationqtp").page("qaautomationqtp").WebEdit("Password").Set DataTable("Password", dtGlobalSheet)

Browser("qaautomationqtp").page("qaautomationqtp").Webbutton("SignIn").Click

In the above example we use the global sheet to drive the data. We can upload data from external excel file or database to datatables in QTP and also extract back the information from data tables to excel using data table import/export methods. Once we have data in the local sheet or global sheet in data table, we can use data as shown in above code in QTP.

By data driven testing using parameterization, we need to create only a single test for 100 of tests using hardcoded approach and also the data is managed at a single place, hence improving the maintenance of the test.

Next question is what all we can parameterize in QTP, we can parameterize :

• Checkpoints properties

• Object properties for a selected step.

• Operation arguments defined for a selected step.

• Object properties in Object Repository.

The different ways in which data can be parameterized in QTP/UFT are as follows:

• Test parameters enable us to use values passed in the test.

• Action parameters enable us to pass values from other actions in the test.

• Using Random Number, we can parameterize the value where any random numeric value can be provided.

• Environment variable can be used for parameterization in QTP

• Using datatables in QTP/UFT, we can parameterize the data. Before running the test we need to specify in Test Setting or action settings in QTP, how many iteration the test needs to be executed

This article "Working with data tables" explains how to work with data tables in details.

This article “Environment variable” explains how to work with environment variable in QTP.

How to send e-mail using outlook object in QTP

This post explain how to send e-mail using microsoft outlook object with or without any attachments. In the next article we will explain how to read information from an e-mail message.

''Function Description - This function sends a mail to end user
'' Arguments - SendtoUser - Mail will be send to user mentioned in send to
''Subject - Subject of the mail
''Body - Text of the mail
''Attachment - Path of attachment to send e-mail
SendMailUsingOutlook "SendToUser@test.com; testZ@test.com"," Subject", "Body", ""

Function SendMailUsingOutlook(SendToUser, Subject, Body, Attachment) 

'' create an object of outlook application and creating a new e-mail object
    Set objOutlook=CreateObject("Outlook.Application") 
    Set objMail=objOutlook.CreateItem(0) 

'' Provide details of the mail object
    objMail.to=SendToUser
    objMail.Subject=Subject 
    objMail.Body=Body 

'' attach a file with the e-mail, provide path of the attachment in the folder
    If (Attachment <> "") Then 
        objMail.Attachments.Add(Attachment) 
    End If 
'' Send the e-mail
    objMail.Send 
'' close the instance of Outlook application object
    objOutlook.Quit 

''release the object created    Set objMail = Nothing 
    Set objOutlook = Nothing 
End Function