QTP/UFT tutorial- Parameterization the simple way 3 (Parameterization Only in Excel)

Hi. In the previous posts, I showed you how to parameterize by importing an excel sheet into the QTP global sheet, as well as just parameterizing direction in your scripts. You can find the tutorials here:

Parameterization In Script

Parameterization Importing Excel

Today, I will show you how to parameterize your scripts using excel alone.

1. In the tutorial here, we created an example excel sheet. We will be using this same sheet for this tutorial. If you don't want to recreate this excel sheet, click on the "Example" below and download it. Make sure to save it in the directory where you will be running your QTP script.

Example

2. First, we want to access the excel application. We can do it with the code below:

 Set objExcel = CreateObject("Excel.Application")

We are setting creating an object objExcel to access the functionality of the Excel application.

3. Next, we want to access the specific excel workbook that we created.

 Set objWorkbook = objExcel.WorkBooks.Open (Environment("TestDir")&"\Example.xlsx") 

We first access our excel object we created, objExcel, then we can use the dot operator to access the workbooks, and then we can use the dot operator again to set the method for what we want to do with the workbook.

objExcel.WorkBooks.Open 

Then we just have to set the path. The path is currently set to our current test directory.

4. Lastly, we have to access the specific sheet within our workbook.

Set objDriverSheet = objWorkbook.Worksheets("TestData")

We can do this through the workbook object we just created. Then set the sheet name to the name of the excel sheet we set the data in. "TestData"

5. To make our parameterization dynamic, we need to get the row count of all of the cells that have data.

 intExcelRowCount = objDriverSheet.UsedRange.Rows.Count

This will tell us that we have four rows including the row name "UserNames"

6. Lastly, let's create a loop that will loop through all the row values within one variable.

 Dim i
 For i = 2 To intExcelRowCount Step 1
     varUserName =  objDriverSheet.Cells(i,"A").Value 
      MsgBox varUserName
 Next

This is the same method as the other parameterization methods I showed you, the only difference is the syntax to access the cell within the excel sheet. I set the our variable to objDriverSheet.Cells(i,"A").Value. I started our loop at 2 because that's where our user name values begin. And every time the loop runs, we should see a different message with a different user name.

Full code:


 

 Set objExcel = CreateObject("Excel.Application")
 Set objWorkbook = objExcel.WorkBooks.Open (Environment("TestDir")&"\Example.xlsx") 
 Set objDriverSheet = objWorkbook.Worksheets("TestData")
 
 intExcelRowCount = objDriverSheet.UsedRange.Rows.Count 

 Dim i
 For i = 2 To intExcelRowCount Step 1
     varUserName =  objDriverSheet.Cells(i,"A").Value 
     MsgBox varUserName
 Next

objExcel.Application.Quit
Set objDriverSheet =nothing
Set objWorkbook = nothing

7. Lastly, make sure to quit the application properly as you see above. First quit excel, then set the worksheet to nothing, following the workbook to nothing. Run your code and you will see the parameterization in action!

Please post your comments and questions below. I am here to help you as best as I can.

 

 

Pin It on Pinterest

Clef two-factor authentication