QTP: Parameterization with Excel

by Anshoo Arora on February 5, 2010

In the previous article, we parameterized a test for a Login process with the help of QTP’s Local DataTable. This topic covers the same concept with the help of an (external) Excel file. The process will remain the same, but how we extract data differs. If you are looking to understand Parameterization concepts and need a quick introduction, I would recommend you to read the following article: Introduction to Parameterization with QTP’s Local DataTable

Before we start the process of extracting data from Excel, and using the extracted data in our automated process, we must first understand the Excel Automation object. Before that even, we must understand the CreateObject function. The CreateObject function creates and returns a reference to an Automation object¹. In other words, usage of CreateObject in terms of Excel will return the reference of Excel’s Automation object. This reference will enable us to use Excel’s methods for data retrieval. Below is the syntax of CreateObject:

Set Var = CreateObject(servername.typename [, location])

The Set keyword is used here because we have to bind out variable to an object. The Set statement assigns an object reference to a variable or property, or associates a procedure reference with an event². We can use the syntax above in terms of Excel and create a reference object that will hold methods of the Excel application:

Dim xlApp	'Excel Application Automation Object Reference

Set xlApp = CreateObject("Excel.Application")

Next, we will create a reference to the Excel WorkBook, which is the file that we will open to retrieve data from. The workbook we’re working with is located in the root folder: C:\ and its name is TestFile.xls. Thus, we will substitute the pathName\fileName in our code below:

Dim xlBook	'Reference to the workbook located in C:\

'Set varExcelWorkBook = ExcelObjectReference.WorkBooks.Open("File")
Set xlBook = xlApp.WorkBooks.Open("C:\TestFile.xls")

Next, let’s create a reference to the Excel Spreadsheet (Sheet1, Sheet2, Sheet3..) which contains the data we need to drive our parameterized script. Our data is contained in Sheet1 of our workbook, thus, we will use it in our code below:

Dim xlSheet	'Reference to Sheet1

'Set varExcelWorkSheet = varExcelWorkBook.WorkSheets("Sheet")
Set xlSheet = xlBook.WorkSheets("Sheet1")

Below is a snapshot of the spreadsheet, with headings in Row 1 and data starting from Row 2:

Sample Excel Table

To retrieve the number of rows and columns used in the table, we will use the code below:

'Total Rows
iRows = xlSheet.UsedRange.Rows.Count
 
'Total Columns
iCols = xlSheet.UsedRange.Columns.Count

Lastly, we will retrieve the data from Excel:

'First UserName is stored in the first column (Column A), second Row (A2) of the spreadsheet
sUserName = xlSheet.Rows(2).Columns(1).Value
 
'First Password is stored in the second column (Column B), second Row (B2) of the spreadsheet
sPassword = xlSheet.Rows(2).Columns(2).Value

I think we have covered the concepts needed to parameterize our script directly from an Excel file. Now, let’s use the same example we did in the previous article to parameterize the Login process for Mercury/HP’s demo AUT.

1. Launch application
2. Enter UserName/Password from DataTable and click Login
3. Verify if the Find Flights page appears
4. If Find Flights page appears, iteration passed.
5. Return to Home Page and start Step #2

'Step 1: Launch Application
SystemUtil.Run "iexplore.exe", "http://newtours.demoaut.com"

'Step 2: Enter UserName/Password from DataTable and click Login

'Retrieve UserName and Password from "iRow" rows and columns A & B
sUserName = xlSheet.Rows(iRow).Columns(iUserNameCol).Value
sPassword = xlSheet.Rows(iRow).Columns(iPasswordCol).Value
 
'Parameterization Block:
With Browser("title:=Welcome: Mercury Tours", "index:=0")
	.WebEdit("name:=userName").Set sUserName    'Parameter 1: UserName
	.WebEdit("name:=password").Set sPassword    'Parameter 2: Password
	.Image("name:=login").Click
End With

'Step 3: Verify if the Find Flights page appears

If Browser("title:=Find a Flight: Mercury Tours:", "index:=0").Exist(10) Then
	'code
End If

'Step 4: If Find Flights page appears, iteration passed.

If Browser("title:=Find a Flight: Mercury Tours:", "index:=0").Exist(10) Then
   Reporter.ReportEvent micPass, "Iteration " & iRow-1, "UserName: " & sUserName & " is valid"
Else
   Reporter.ReportEvent micFail, "Iteration " & iRow-1, "UserName: " & sUserName & " is invalid"
End If

'Step 5: Return to Home Page and start Step #2

If Browser("title:=Find a Flight: Mercury Tours:", "index:=0").Exist(10) Then
   Browser("title:=Find a Flight: Mercury Tours:", "index:=0").Link("text:=Home").Click
   Reporter.ReportEvent micPass, "Iteration " & iRow-1, "UserName: " sUserName & " is valid"
Else
   Reporter.ReportEvent micFail, "Iteration " & iRow-1, "UserName: " &sUserName& " is invalid"
   Browser("title:=Sign-on: Mercury Tours", "index:=0").Link("text:=Home").Click
End If

Steps 1-5
We can now combine all the 5 steps into a single code block:

'Steps 2-5

Dim xlApp, xlBook, xlSheet
Dim iRow, sUserName, sPassword
CONST iUserNameCol = 1	'UserName is in Column A
CONST iPasswordCol = 2	'Password is in Column B

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.WorkBooks.Open("C:\TestFile.xls")
Set xlSheet = xlBook.WorkSheets("Sheet1")
 
'iRow = 2 because data to be driven starts from Row #2
For iRow = 2 to xlSheet.UsedRange.Rows.Count
 
   'Retrieve UserName and Password from "iRow" rows and columns A & B
   sUserName = xlSheet.Rows(iRow).Columns(iUserNameCol).Value
   sPassword = xlSheet.Rows(iRow).Columns(iPasswordCol).Value
 
   'Parameterization Block:
   With Browser("title:=Welcome: Mercury Tours", "index:=0")
      'Step 2
      .WebEdit("name:=userName").Set sUserName   'Parameter 1: UserName
      .WebEdit("name:=password").Set sPassword   'Parameter 2: Password
      .Image("name:=login").Click
   End With   
 
   'Step 3: If Find a Flight page appears, go back to Home
   If Browser("title:=Find a Flight: Mercury Tours:", "index:=0").Exist(10) Then
      Browser("title:=Find a Flight: Mercury Tours:", "index:=0").Link("text:=Home").Click
 
      'Step 4: Pass the iteration
      Reporter.ReportEvent micPass, "Iteration " & iRow-1, "UserName: " &sUserName& " is valid"
   Else
      'Step 5: Fail the iteration and return to the Home page
      Reporter.ReportEvent micFail, "Iteration " & iRow-1, "UserName: " &sUserName& " is invalid"
      Browser("title:=Sign-on: Mercury Tours", "index:=0").Link("text:=Home").Click
   End If   
 
Next
 
xlBook.Close
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

When the above code executes, the follow results will be populated in QTP:

Parameterization with Excel: Results

I hope this article will help automation developers create parameterization with Excel. The concepts covered in this article are quite basic, but they should serve as a baseline to creating parameterization through Excel which can give users more control over how they data-drive their tests. In the coming articles, I will show how Excel can be used as a data-source with performance that QTP’s DataTable just doesn’t provide.

I hope you find this useful :)

References
1. CreateObject Function – MSDN
2. QuickTest Professional Reference Help File – Set Statement

If you have any questions, please ask them in the comments section. If your query is confidential, please use the Contact Form to send me an e-mail instead.

Tagged as: Parameterization, Parameterization with Excel, QTP Excel Parameterization, QTP Parameterization

{ 60 comments… read them below or add one }

1 padma February 5, 2010 at 7:12 pm

what if i need to change the URL every time?

2 Anshoo Arora February 7, 2010 at 8:00 am

Hi Padma,

There are several things to consider if the URL changes:

1. Are the object descriptions changing?
+ If no, then only the URL would need to be parameterized. The new table will now look like this:

UserName

Password

URL

+ If yes, the object descriptions would need to be parameterized along with the URL. The new table will now look like this:

UserName

Password

URL

description_UserName

description_Password

3 !ndra February 6, 2010 at 12:27 am

Thanks for the post. Very clearly articulated. As you said its quite basic, May I know what are the advanced concepts in this topic.
Have a great time. Keep Smiling.

4 Anshoo Arora February 7, 2010 at 8:01 am: Hi Indra,

Thank you. :)

I have a few drafts that I need to finalize. They should be out soon :)

Reply

5 Anonymous February 6, 2010 at 12:45 am

HI Anshu,

Great Article!!!
We are looking forward for Advanced Articles.

6 Anshoo Arora February 7, 2010 at 8:03 am: Thank you. :)

The advanced articles on this topic should be out soon :)

Reply

7 DEVENDRA SHARMA February 8, 2010 at 2:32 am

Hi Anshoo,

I got a chance to visit the “.Find” method like: “Obj_WSheet.UsedRange.Find ()” to find the Text from the WorkSheet.
It would be nice, if you could explain this method.

Thanks in Advance!!

Regards,
Devendra Sharma

8 Anshoo Arora February 10, 2010 at 12:14 am

Hi Devendra,

The .Find method returns the ‘Found Cell’ object. This can be converted into its respective Column/Row values. For example, if you have an Excel sheet with the following cells:

UserName | Password | Age | Gender | SSN

and you would like to find the destination cell for Password, you could use the find in the following manner:

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Test.xls")
Set xlSheet = xlBook.Worksheets("Sheet1")
Set oRange = xlSheet.UsedRange

Set oCell = oRange.Find("Password")

MsgBox oCell.Column
MsgBox oCell.Row

xlBook.Close
xlApp.Quit
Set xlSheet = Nothing
Set oRange = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

This will give you the corresponding Row and Column entities for the string.

9 DEVENDRA SHARMA February 10, 2010 at 2:51 am

Thanks Anshoo!!

UserName	  Password	       Age	Gender	SSN
dsharma	  Test123	       25	M	123-456-7890
pdixit	  Test123	       20	M	109-874-5790

“.Find” method gives only the First occurance of the Pasword “Test123″, now I want to get all the occorance of the password “Test123″. I feel we can do this with the help of “.FindNext” method. It would be nice, if you could please expain “.FindNext” method.

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Test.xls")
Set xlSheet = xlBook.Worksheets("Sheet1")
Set oRange = xlSheet.UsedRange

Set oCell = oRange.Find("Test123")
MsgBox oCell.Column
MsgBox oCell.Row

xlBook.Close
xlApp.Quit
Set xlSheet = Nothing
Set oRange = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

Regards,
Devendra Sharma

10 Anshoo Arora February 10, 2010 at 3:04 am: Devendra,

You can use .FindNext to find the next occurrence of the string.

Reply

11 DEVENDRA SHARMA February 10, 2010 at 3:00 am

Please find the Test.xls:

UserName | Password | Age | Gender | SSN
dsharma	| Test123	|   25     |	M    | 123-456-7890
pdixit	| Test123	|   20     |     M    | 109-874-5790

12 DEVENDRA SHARMA February 10, 2010 at 3:28 am

Hi Anshoo,

When I am executing the below code, it thows the following error message.

ERROR: Unable to get the FindNext Property of the Range Class.

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible=True
Set xlBook = xlApp.Workbooks.Open("C:\Test.xls")
Set xlSheet = xlBook.Worksheets("Sheet1")
Set oRange = xlSheet.UsedRange

For i=0 to 10 

Set oCell = oRange.Find("Test123")
oCell.Interior.ColorIndex=25
MsgBox oCell.Column
MsgBox oCell.Row

Set oCell=oRange.FindNext("Test123")

Next

xlBook.Close
xlApp.Quit
Set xlSheet = Nothing
Set oRange = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

Thanks,
Devendra Sharma

13 Anshoo Arora February 10, 2010 at 4:01 am

When you use .FindNext, you would need to provide the original reference that you used the first time you used .Find. So, it should like this:

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible=True
Set xlBook = xlApp.Workbooks.Open("C:\Test.xls")
Set xlSheet = xlBook.Worksheets("Sheet1")
Set oRange = xlSheet.UsedRange

Set oCell = oRange.Find("Test123")

For i = 0 to 10
	oCell.Interior.ColorIndex=25

	MsgBox oCell.Column
	MsgBox oCell.Row

        'Notice the oCell here. The object reference has been used instead of the string
	Set oCell = oRange.FindNext(oCell)
Next

xlBook.Close
xlApp.Quit
Set xlSheet = Nothing
Set oRange = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

14 DEVENDRA SHARMA February 10, 2010 at 5:52 am

Hello Anshoo – Thanks a lot!!! for your co-operation.

This code is working fine. But I am facing one problem, if the text be searched is in the following rows like in 1st Row, 25th Row and 90th Row then the below code first find all the text and then iterate the loop up to 90 times. This creates a performance issue.

Please suggest how we can setup our loop so that it can only be iterate as the number of Text to be searched.
For E.g: if in the file “Test123″ is placed only 4 places with in the 90 rows then loop should be iterate only 4 times.

For i = 0 to No_Rows-1

oCell.Interior.ColorIndex=40

‘MsgBox oCell.Column
‘MsgBox oCell.Row
Msgbox “No of Iteration #” &i

‘Notice the oCell here. The object reference has been used instead of the string
Set oCell = oRange.FindNext(oCell)

Regards,
Devendra Sharma

15 Anshoo Arora February 10, 2010 at 2:06 pm: You’re right.. the code will indeed search sequentially. I don’t think there is a way to use this method to search in a way that suits performance, which is why I never use this technique to retrieve data from Excel. Please refer to this article: http://relevantcodes.com/qtp-creating-a-highly-efficient-test-data-dictionary/ which shows a way to retrieve data, build a dictionary object from it at excellent performance. I really like that technique, and 90% of the times I use either that or replace the ADO query with a Range object (as shown in RelevantCodes[1]One Framework).

Reply

16 DEVENDRA SHARMA February 10, 2010 at 2:40 pm

Thanks a lot Anshoo!!!

17 Anshoo Arora February 10, 2010 at 2:42 pm: :)

Reply

18 Deepak May 10, 2010 at 1:48 am

Hi Anshoo,

Thanks for your email, Great job. I got query regarding one of the scenario, I want to place one variable (which will be generated runtime), Currently i am capturing that variable and using the MsgBox to display that variable. but i wnated that variable to be moved into excel file TestExcel.xls into Sheet1 and Row 2, Column = 2 .(This would be used to run next test)

Thanks in advance guys

Deepak

19 Anshoo Arora May 10, 2010 at 10:24 am

Deepak,

try this:

Set oExcel = CreateObject("Excel.Application")
Set oWorkbook = oExcel.Workbooks.Open("C:\TestExcel.xls")
Set oWorksheet = oWorkBook.Worksheets("Sheet1")

oWorksheet.Rows(2).Columns(2).Value = Variable

oWorkbook.Save
oExcel.Quit

Set oWorksheet = Nothing
Set oWorkbook = Nothing
Set oExcel = Nothing

20 Deepak May 10, 2010 at 7:33 pm

Thanks a lot Anshoo. You are too fast in the replies. We liked you somuch

21 Deepak May 11, 2010 at 8:42 pm

Hi Anshoo,

We couldn’t find much information anywhere about the use of XML file as Test Data for large sets of data.

DO you have any plans of including this in your website. Thanks in advance.

Deepak

22 Anshoo Arora May 12, 2010 at 10:22 am: With large sets of data, XML files can be quick painful to maintain and understand which is why I usually prefer a tabular format :)

I will try to write an article on working with XML files though, which should cover this as well.

Reply

23 Deepak May 12, 2010 at 2:44 am

Hi Anshoo,

I have got a strange question for you. We have been working on a Java based applet application, We are using QTP 10.0 with Java addin and all the java patches. we are using excel to populate the data, we have tested this application for hundreds of times with some data, but when we suddenly increased the number of rows of data, it is exactly stopping at row 83, We have tested with different sets of data. It is stopping exactly at row 83. My question is while the excel spreadsheet is loading the data in data table and run the test, Is there any limits that were there on the data table to handle the data, Is it a memory related issue with data table. Is there any solution to fix these kinds of strange behaviours of Excel sheet / QTP Data table. (There is absolutely nothing wrong in the data we entered for all the columns, it is working fine for upto row 82, when it comes to row 83 it is stopping on some object)

Have you guys experienced like this scenario before

Thanks,

Deepak

24 Anshoo Arora May 12, 2010 at 10:20 am: I just ran a test with 99 rows of data and no such issue.. Not sure what might be causing this. Is it a data issue? Have you tried switching rows around? Does it stop at the exact same point each time?

Reply

25 Deepak May 12, 2010 at 6:35 pm

Hi Anshoo,

I have tried it with different sets of data. It exactly stopped there. It is not a data related issue.

Thanks for yoiur promt reply

26 Anshoo Arora May 14, 2010 at 9:29 am: Deepak,

Not sure what the issue can be. It can be multiple things. If you have eliminated all user error possibilities, and feel its a QTP issue, then you may need to contact HP support to get this resolved.

Reply

27 Deepak May 16, 2010 at 7:20 pm

Hi Anshoo,

Thanks anshoo for your reply.
I think i found the problem for this. After i run with 100 rows of data, at certain point above 80 rows it is hanging on the script. It looks like a bug in the application not QTP because, From the point where it stopped i did a navigate and learn and it is capturing the same objects for 83 rows. Fo example there is a Help button on side bar, but it captured it for more than 80 times as object. And when i clicked the button “Highlight in Application” in object repository manager, to test some of the objects it is trying to capture the objects which were not visible on the screen, but it is trying to highlight.

Thanks

Deepak

28 Anshoo Arora May 18, 2010 at 7:55 am: Deepak: Glad you figured this out. Yeah, I didn’t think it would be a QTP issue because I’ve run tests with tons of iterations and they’ve run without any issues. Well, QTP did crash once or twice, but other than that, its been quite smooth.

Reply

29 Rohan May 17, 2010 at 1:19 am

Hi Anshoo,

I had a query regarding Excel, Currently i created a some modules as individual QTP scripts and every module has its own excel file as test data in “Sheet1″ of corresponding excel. I would like to merge all the test data excel files into one master excel file with different sheet names and use them in my tests like sheet1 would be renamed “Registration” Sheet2 will be renamed to “Management” etc…..Could anybody have any idea how this can be implemented in QTP.

Thanks in advance

Rohan

30 Anshoo Arora May 18, 2010 at 7:58 am: Rohan,

Would you like to move all sheets to a single workbook through QTP? Or, would you like to add a new sheet each time your test is run with a different name?

Reply

31 Rohan May 18, 2010 at 7:39 pm

I would like to place all the test data sheets in one single excel workbook, currently they are in 10 different excel files (I want do this manually) but i wanted the tests to be run with different sheet names in one single excel workbook.

Thanks Anshoo

32 Anshoo Arora May 26, 2010 at 8:20 am

Rohan,

If you’re trying to change the name of the sheet, you can follow this code:

Set xl = CreateObject("Excel.Application")
Set book = xl.Workbooks.Open("C:\Test1.xls")

book.Worksheets("Sheet1").Name = "Registration"

book.Save
book.Close
xl.Quit

Set book = Nothing
Set xl = Nothing

If you would like to change multiple sheet names at once, you can create a Scripting.Dictionary and add the present sheet names and the names that you would like to change like this:

Set sheets = CreateObject("Scripting.Dictionary")
sheets.Add "Sheet1", "Registration"
sheets.Add "Sheet2", "Management"

Set xl = CreateObject("Excel.Application")
Set book = xl.Workbooks.Open("C:\Test1.xls")

keys = sheets.keys
items = sheets.items

For ix = 0 to sheets.Count - 1
	book.Worksheets(keys(ix)).Name = items(ix)
Next

book.Save
book.Close
xl.Quit

Set book = Nothing
Set xl = Nothing

33 rohan May 28, 2010 at 5:49 am: Thanks heaps Anshoo

Reply

34 chopra May 19, 2010 at 2:31 pm

Actual QTP script,

so when I call login(Manager), it should get the values of Manager (ABC and XYZ ) and put that for the same user name and password.
browser().Page().Webedit().Set Username (from that function)
browser().Page().webedit().set password ( from that function)
click.login

I would appreciate your earliest response.

35 Anshoo Arora May 26, 2010 at 8:26 am

Chopra,

You can try something like this:

Function Login(sRole)
	Dim Roles, Keys, ix

	Set Roles = CreateObject("Scripting.Dictionary")
	Roles.Add "Manager", "Username|Password"
	Roles.Add "Accountant", "Username|Password"

	Keys = Roles.Keys

	For ix = 0 to Roles.Count - 1
		If LCase(sRole) = LCase(Keys(ix)) Then
			sUserName = Split(Roles.Item(Keys(ix)), "|")(0)
			sPassword = Split(Roles.Item(Keys(ix)), "|")(1)
		End If
	Next

	If sUserName <> "" And sPassword <> "" Then
    	Browser().Page().WebEdit().Set sUserName
		Browser().Page().WebEdit().Set sPassword
	End If

	Set Roles = Nothing
End Function

Usage:

36 Chopra May 26, 2010 at 2:46 pm

Dear Anshoo. this worked perfect. this was awesome.

I will tried playing your code by getting the values from excel with 3 columns (Roles, Username, Password) but was getting no results.
I am still trying, but if I can use your expertise I would really appreciate.

Regards

Mohit Chopra

37 Anshoo Arora June 1, 2010 at 5:25 am: Mohit,

What is the code that you tried? Excel COM object will be required to retrieve data, and you may need to run it in a loop in order to parametrize it.

Reply

38 Sumit patra May 20, 2010 at 8:37 am

How to check one row of the data table toa entair excel sheet.
For example i have the value A=10 in my global sheet ,now i want to check the value 10 in a excel sheet .(it may not in the same row ).

39 Anshoo Arora May 26, 2010 at 8:38 am

To check for a value in an Excel Sheet, you can use the following:

Function CheckValue(sValue, sFileName, vSheet)
	Dim xl, book, sheet, range, found

	CheckValue = False

	Set xl = CreateObject("Excel.Application")
	Set book = xl.Workbooks.Open(sFileName)
	Set sheet = book.Worksheets(vSheet)
	Set range = sheet.UsedRange

	Set found = range.Find(sValue)

	On Error Resume Next
		If Not found Is Nothing Then CheckValue = True
	On Error Goto 0

	book.Close
	xl.Quit

	Set sheet = Nothing
	Set book = Nothing
	Set xl = Nothing
End Function

Usage:

MsgBox CheckValue("10", "C:\Test.xls", "Sheet1")

40 Rohan May 28, 2010 at 6:00 am

Hi Anshoo, we have a strange problem thats occuring for only some of the Excel files when used in QTP as test data. QTP test is trying to process the empty row as well. For example there are 10 rows of test data are there in excel and while running the test after finishing the 10 rows, QTP is trying to run on the 11th row (empty row) as well so the test is not finishing. Previously whenever this happened we have selected the empty rows from bottom of the excel sheet and deleted the empty rows. it used to work fine but the problem started again. Is there any solution to handle empty rows scenario like this.

41 Anshoo Arora June 1, 2010 at 5:27 am: Rohan,

Open the Excel sheet, select the last few empty rows and delete them. Even if you have accessed an Excel cell, which may have been left empty will be counted as a used cell. Either that, or you will have to check each data retrieved is null or not. I think deleting all the empty rows towards the end is going to be a better approach though.

Reply

42 Rohan June 2, 2010 at 3:34 am

Hi Anshoo,

Thanks for your reply. Sometimes after deleting the empty rows also it is trying to process on the empty rows. We don’t know the exact reason why it is happening. Can we do something with QTP/VB scripting to handle this. The problem occuring during the regression, some of the modules is stucking up on empty rows and test is not going to next module due to this. I hope this can be handled through script.

With best regards

Rohan

43 Anshoo Arora June 7, 2010 at 8:44 am: Can you please share the function/code that you are using to write to Excel?

Reply

44 Al June 29, 2010 at 4:50 am

Hi Anshoo,

I need a solution to for using wild card for parametrization in datasheet.
I have a dropdown which has some values which have been parametrised to be picked up from the data sheet.
Everything is fine upto this point. The problem now arises when the data in the dropdown is dynamic and changes with each run.

e.g. if i am trying to use just “one” vlaue in the drop down everytime I run it, it looks like this

For 1st run: Abc def 463.98
For 2nd run: Abc def 465.34

First few characters(in this case first 8 characters) will “always” remain constant for all the runs. And just to reiterate I can safely ignore the variable part (last 6 characters) in all my runs.

Question is – How can I use a wild card kind of thing in the datasheet so that it will work with each run.

Any help from your side is greatly appreciated.

-Thanks
Al

45 Anshoo Arora July 6, 2010 at 1:44 pm

Hi Al,

You can setup your DataTable to have the following values:

Abc def 463.*
Abc def 465.*
Abc def 465\.\d+

and see how to use ReGex with the Select method in this article.

46 sree July 13, 2010 at 3:47 pm

Really Great website

47 Anshoo Arora July 14, 2010 at 3:18 pm: Thank you!

Reply

48 Jakka August 3, 2010 at 6:26 am

Browser(“title:=Welcome: Mercury Tours”, “index:=0″) .WebEdit(“name:=userName”).Set sUserName

sUserName = for this variable we are getting data from excelsheet from step 1-5

1) if 1 or 2 set control means no problem
1) If there are many set control in a program, then what we have to do

49 Anshoo Arora August 5, 2010 at 2:00 pm: Jakka,

I’m not sure if I understood your question. Can you please elaborate?

Reply

50 Deepak August 3, 2010 at 12:40 pm

Hi Anshoo,

I went through all your posts and comments. All these are quite elaborate. I may ask you some irritating questions, please answer me those with example.

What is Action parameters, datatable parameters, How it is useful in Parameterization. Could you please provide your answer with examples.

Thanks
Deepak

51 arpita August 4, 2010 at 3:48 am

hi anshoo,
i want to retrive a particular cell from excel sheet using qtp scrpt?
how to retive?

52 Anshoo Arora August 5, 2010 at 2:05 pm

Arpita,

With QTP, you can retrieve the entire sheet and import it to the DataTable. If you just want to retrieve a single cell, then you can use the following:

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.WorkBooks.Open("C:\Test.xls") 'remember to update the correct path
Set xlSheet = xlBook.WorkSheets("Sheet1")

theValue = xlSheet.Rows(1).Columns(1).Value

xlApp.Quit

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

53 arpita August 4, 2010 at 3:52 am

Hi anshoo,
I want to retrive a row from excel sheet like
username :arpita
pwd :mercury
servername:@yahii.com
port:1243
how to retrve can u suggest me some script using qtp

54 Anonymous August 6, 2010 at 2:54 am

Hi Anshoo
i tried your code.i put 8 in row & E in column.But it shows error unknown runtime error.
can u tell me pls?

can u suggest me any answer for 2nd one?

Thanks
arpita

55 Anshoo Arora August 8, 2010 at 7:44 pm: Try using 5 for the column instead of E. Does that work?

Reply

56 Arpita August 9, 2010 at 2:47 am

Anshoo,
I am using 5 also its not working.Can u sugest me any other method.
Another thing is i want to retrive a row from excel sheet.suppose i am putting values in 4th row & 5th column
like username:arpi
pwd:mercury
port:1209
pls reply me ..

Thanks

57 Anshoo Arora August 9, 2010 at 8:50 am

Arpita,

To store values in Excel, the value to be written should be on the right side of the = sign:

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Test.xls")
Set xlSheet = xlBook.Worksheets("Sheet1")

xlSheet.Rows(2).Columns(1).Value = "Row2Column1"

xlBook.Save
xlApp.Quit

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

When the value is to be retrieved, the variable that stores the value should be on the left side of the = sign:

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Test.xls")
Set xlSheet = xlBook.Worksheets("Sheet1")

sValue = xlSheet.Rows(2).Columns(1).Value
MsgBox sValue

xlBook.Save
xlApp.Quit

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

PS. Please make sure to update the Workbook path and the Sheet name before using the above 2 snippets.

58 Arpita August 10, 2010 at 11:19 am

Thanks Anshoo
Your tips really helped me…

59 mini August 30, 2010 at 1:13 am

Hi Anshoo,
I want to retrive QTP result in an excel sheet.how to retrive ?
can u help me?

60 Anshoo Arora September 2, 2010 at 7:39 am: Mini,

Can you please elaborate upon your requirement? Do you mean, you want to generate a report in .xls format?

Reply

Relevant Codes

QTP: Parameterization with Excel

Default Widget

Default Widget

Recent Comments

Subscribe

Relevant Codes

QTP: Parameterization with Excel

Default Widget

Default Widget

Tags

Recent Comments

Subscribe