QTP – Parametrization with Excel

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 object1. 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 event2. 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:

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")
 
'Step 1: Launch Application
SystemUtil.Run "iexplore.exe", "http://newtours.demoaut.com"
 
'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

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.

Subscribe for Relevant Codes newsletter to get awesome updates and articles delivered straight to your inbox.

Leave a Comment

{ 156 comments… add one }

  • amol March 6, 2014, 6:00 am

    hi,
    If I want skip perticular ROW In EXCEL during execution Feg. out of 10 rows i want to skip row 4 , so how can i skip perticular row??? W.r.t. QTP Excel connectivity and we running script by giveing input by Excel..

    thanks in advance

    Reply
  • Sudhir December 29, 2013, 6:10 am

    Why is parametrization using external excel file preferred over internal data table ?

    Reply
  • Krish September 25, 2013, 11:04 am

    Hi,

    120 databases have to be tested. Script begins with logging into the application, selecting a database and running test suite.

    Help needed from you:
    1. Select a db from application
    2. Run test suite
    3. Pick next db, run test suite and so on

    Thanks Anshoo

    Reply
  • Nishanth March 14, 2013, 1:50 pm

    Hi Anshoo,

    Does QTP 11 support Import and Export methods with excel 2007(.xlsx)?
    when I googled I saw answers all of them which say that qtp supports only .xls format for export and import. but these answers are a year old.

    when i tried…these methods worked only with .xls. So does QTP 11 still supports only .xls or are there any patches for it so that we can use .xlsx.

    thanks,
    nishanth

    Reply
  • Anil February 6, 2013, 5:17 am

    Hi Anshoo,

    I have values in the excel drop down, when I run the QTP script, once the script is executed and when I open the excel sheet, the drop down is no more there in the excel sheet.
    QTP is changing the excel format? If yes, how to keep the drop down intact in the excel even after qtp executes all the scripts?

    Regards,
    Anil

    Reply
  • abhishek December 4, 2012, 5:54 am

    Hi,
    I want working on a excel which have around 20k records. I am using Dictionary object for it but it is taking a lot of time around 15 mins to load the excel in the dictioanry.
    So can you suggest me any other way which can help me to do my task in less time.

    Reply
    • Anil December 4, 2012, 6:00 am

      Hi Abhishek,

      What do you mean “Load excel in dictionary”?
      You mean load the excel file to qtp data table?

      Regards,
      Anil

    • Anonymous December 5, 2012, 11:51 am

      Hi Anil ,
      No it is not lodinging in the datatable, it is basically loading the whole excel in the Dictionary object .
      actully i have to use only 2 rows of the excel and in which one row contains the unique codes so i created a dictionary object.
      Set dict = CreateObject(“Scripting.Dictionary”)
      and in which key is 1st row which have uniue data and item is the 2nd row.
      Now the issue is the size of the excel.so it take around 15 mins to load the whole excel into this object .
      so cn you please suggest me any other work around which took lesser time .

    • Anil December 6, 2012, 12:01 am

      Hi,

      Instead of loading excel data to dictionary and again read from dictionary, I think you can use as below…
      Set Excel = CreateObject(“Excel.Application”)
      with this you can directly read the data from excel which will be much faster. You can store the values in dynamic array and read from that array.

      Hope this helps!

      Regards,
      Anil

  • Anil November 26, 2012, 2:30 am

    Hi,

    I have values in the excel drop down, when I run the QTP script, once the script is executed and when I open the excel sheet, the drop down is no more there in the excel sheet.
    QTP is changing the excel format? If yes, how to keep the drop down intact in the excel even after qtp executes all the scripts?

    Regards,
    Anil

    Reply
  • abc November 17, 2012, 4:50 am

    Please can anyone tell me how to use dictionary object with excel to retrieve data

    Reply
  • oNKAR September 17, 2012, 12:55 am

    Hi,

    i am using qtp 10.0 With Active X Web and Visual basic add-ins and Office 2010 but it is not recognizing Excel.application QTP throws error with object not recognized

    Reply
  • Rajesh May 10, 2012, 3:24 pm

    Hi Anshoo,

    I need information of what are the different “Type Libraries” that can be used in QTP . I am also looking for some internal information of those type libraries. where can i find this info in online (any website link)?

    Reply
    • Anshoo Arora June 2, 2012, 5:47 am

      Rajesh, what does it mean by “type libraries used in QTP”? Do you mean .NET typelibs?

  • Aniket May 10, 2012, 10:29 am

    How do I copy the entire column but skip the first row?

    Assuming all bojects are already created, here is the code to copy column from one sheet and paste it to another sheet in same file.

    Set objRange=objBook.Worksheets(“Action1″).Range(“D1″).EntireColumn
    objRange.Copy

    objBook.Worksheets(“Action2″).Range(“D1″).PasteSpecial Paste =xlValues

    It copies the entire column, if only I want to skip first row , how do I do that?

    Thank you

    Reply
  • Prashant Patel April 26, 2012, 4:20 am

    Hi Anshoo

    I just started my career in Automation Testing.I wanted to know that what are the areas i should stress more.

    Prashant

    Reply
  • Anuj March 9, 2012, 2:02 am

    Hi,

    I want to update the excel when the excel is opened. Anyone has any idea about that..

    Thanks,
    Anuj

    Reply
    • Anshoo Arora March 15, 2012, 9:14 am

      Anuj, you can bind to Excel if its instance is open (or will open if closed) using GetObject.

  • Mallikarjun February 29, 2012, 5:04 am

    Hi Anshoo,

    Pls help me out in the below scenario.

    Ex- Firstly we order a book or some thing in external portal and in the final stage, Order ID wil be generated. So here how can i extract that Order ID and store in qtp and use the same Order ID again in internal portal..

    Thanks in advance.

    Reply
    • Anshoo Arora March 15, 2012, 8:33 am

      Mallikarjun, there are several ways you can do this:

      1. Store the value in an Environment variable
      2. Store the value in a Global variable
      3. Store the value externally – text file, xml file, xls file etc.

  • karan January 27, 2012, 11:56 pm

    tHANKS

    Reply
  • naveen January 21, 2012, 10:42 pm

    Hi how to validate the present in the excel sheet.
    i mean in sheet i have 3 fields ie’s- name: ,password, url
    how to check is it hyperlink or plain text
    ls answer any one

    Reply
  • Krishna December 19, 2011, 1:41 am

    Firstly, I enter first name and last name, hit search button, to find data in another web page with address and other details for that user. Secondly, I want to compare address populated in the fields of that web page with the address present in the table existing at back end for nearly 10k users.
    Please suggest how to proceed

    Reply
  • mahesh December 13, 2011, 4:20 am

    Hello Ansoo,

    I need some assistance, I would like to check the excel sheet whether the required string (“some name”) is present in the excel sheet or not, and also I would like to know how many times it displayed.

    and need to validate the dates–the dates must be in B\W particular period like 12 months

    Please assist
    Regards
    Umamahesh

    Reply
  • Vijay November 21, 2011, 12:52 pm

    Hi Anshoo,

    I need your help am trying to automate to fill the forms at atime using qtp.

    My form contains 6 fields and these fields are like customer,customername,ID,mail address,phone number and these are differnt from one customer to another . All will put in excel in local desktop and QTP recognises and filled the form automatically.

    Username mailAddress ID Name Phone Number Account Number Address
    smith 5678 Smith 986712352 NA NA
    zak

    Reply
    • mahesh December 13, 2011, 4:13 am

      Hi Zak,

      Just put the 7 th field as “status” and when you create a form in the 7th filed give out put “completed”

      next time when you run use if condition to get start from the fields which have not used

      Regards
      Umamahesh

    • Anshoo Arora December 27, 2011, 6:48 am

      Vijay, in your Excel file, you can create data that is customer specified under the following columns: Customer, Customer Name, ID, Mail Address, Phone Number etc. Once you start filling data, first match in Excel the customer you’re trying to fill out. Once you find the customer in the Excel file, use the row index to drive data to your app.

  • Senthil November 7, 2011, 10:16 am

    Hi,

    I want to calculate number of rows in a particular colummn

    Case1 case2 case3 case4
    abc abc sbv wer
    def hiu wer
    ghi uji
    jkl

    i want to find row count in each column

    Reply
    • Anshoo Arora December 27, 2011, 6:31 am

      Senthil: I would assume your question is for a WebTable. You can use the ColumnCount method to do so. Syntax:

      object.ColumnCount (Row)
      
  • Sushil November 7, 2011, 4:56 am

    hello! Anshoo
    How to copy weblist items into excel sheet…

    Reply
    • Anshoo Arora December 27, 2011, 6:28 am

      Sushil: Retrieve the items using ‘all items’ property. Use Excel COM to either paste the entire string retrieved from ‘all items’ or split them and paste them in different cells.

  • Aastha Gupta October 16, 2011, 4:33 am

    hey anshoo,
    thanks alot for ur articles on QTP, as a beginner I find them very useful :) looing forward for more articles.

    Reply
  • Sanjay September 28, 2011, 1:30 am

    Hi Anshoo,

    Hats off to you and your work… :) ..

    I am new reader of your post.:)
    I WONT only appricitae if i will come across some thing new info i will share by this post…

    You are simply genius

    Thanks,
    Sanjay

    Reply
  • Robert G September 24, 2011, 5:28 pm

    Hi Anshoo,

    I have been following your QTP postings. You provide a very high quality information. I have a question. I create a Test with 10 actions which does login into a website and goes thru a workflow which involves opening few beowser windows and considerable amount of data. It currently runs fine for one iteration. Now I want to put all my data into an Excel file and use it for multiple iterations. Can you suggest what is the best option? Is it better to write a script which loads the data from Excel file and then loops thru and runs the Test (all actions) for multiple iterations? Your help is greatly appreciated.

    Thanks,
    Robert.

    Reply
    • Anshoo Arora September 24, 2011, 9:49 pm

      Robert,

      If all your Actions complete 1 workflow, then I would recommend using just the Global DataSheet and adding the relevant data to it. Once this is done, you can set your test to execute all of Global DataTable’s rows. The affect this will have is:

      1. Test starts
      2. Iteration1
      -> Action1 executes using data in GlobalSheet (DataTable.Value(“Column”, dtGlobalSheet)
      -> Action2 executes using data in GlobalSheet (DataTable.Value(“Column”, dtGlobalSheet)
      -> ActionN executes using data in GlobalSheet (DataTable.Value(“Column”, dtGlobalSheet)
      3. Iteration2
      -> Action1 executes using data in GlobalSheet (DataTable.Value(“Column”, dtGlobalSheet)
      -> Action2 executes using data in GlobalSheet (DataTable.Value(“Column”, dtGlobalSheet)
      -> ActionN executes using data in GlobalSheet (DataTable.Value(“Column”, dtGlobalSheet)

  • Anonymous September 21, 2011, 1:34 am

    Hello,

    I sorry perd advanced if my english look’s like bad, i hope you will understand that i want to explain.

    Thanks to your article, very easy to understand.

    I’m using Qtp 9.5 and i get a issue in a test using excel. This test is a performance test about executing time for a current developpement software and i want to write in an excel sheet the result so.

    I import my sheet using ImportSheet method
    I get time and write it in a data table, then i export via exportsheet method.

    When i try to open my updated file i get a “Data may have been lost” on a pop-up excel.

    I search for few hour before find the problem. It’s probably because i write some macro on my excel document, with user form to make my file easy to use.
    So i can saw that Qtp delete all of my macro after the test.

    My question is, Do you know why qtp erease my macro ? The sheet concerning by the macro wasn’t imported on my Qtp script.

    I hope you understand.

    Regards

    Reply
    • Anshoo Arora September 24, 2011, 9:31 pm

      Hello,

      I have witnessed this behavior and you are right in saying that when you “Import” the DataTable to QTP, it will get rid of all macros + all the formatting. When QTP exports it back to your system (drive), it will remove all the formatting + all the macros. What you will see after exporting is the same format you do in the design-time table.

      I do not know a way that can revert this behavior. This is one of the biggest reasons why I have not stuck with DataTable, but used my own techniques using Excel COM/ADO to make better use of it while keeping all my formatting + macros.

  • Navdeep September 15, 2011, 11:27 am

    Hi Anshoo,

    How can i fetch the Data from excel,Everytime when i am running my script,But i want to capture diffrent Data not a same one

    Order State
    vgd7 Mi
    6yhy NY
    7347 GA

    I want this data on my script but when i am trying to run the script it’s take 1 row value and then start a New test.But i want a Data comes on my script Automatically diffrent only 1.When i am trying to use my script.Please help

    Reply
    • Anshoo Arora September 24, 2011, 8:33 pm

      Navdeep: Do you mean to use this through the DataTable? If yes, you can try the SetNextRow or the SetCurrentRow(Row) methods.

  • sreekanth September 15, 2011, 10:58 am

    Hi Sir
    The information which u provided is more useful to me.
    Please notify me the followup comments vis e-mail.

    Reply
  • pratyusha August 24, 2011, 10:48 am

    write a script to create multiple users in gmail by taking data present in excel file

    Reply
  • Radhika August 11, 2011, 2:52 pm

    Hi Anshoo,

    can you please explain the difference between DATA DRIVEN and PARAMETERIZATION with an example.

    Thanks
    Radhika

    Reply
    • Anshoo Arora August 16, 2011, 8:46 pm

      Radhika, I can only say that parameterization makes it possible to build a data-driven structure :)

  • SK July 28, 2011, 4:15 pm

    Very nicely written , thanks

    Reply
  • Robert June 29, 2011, 9:38 am

    Awesome work Anshoo…

    I would really like to see your framework using the OR instead of DP. I know your said with a little tweaking we can get this accomplished, but it would difficult for us to dive through your code and figure where to enhance it.

    I guess I have to wait on the next version…

    Regards,
    Robert

    Reply
    • Anshoo Arora June 29, 2011, 7:19 pm

      Robert: I do have plans to creating another framework for the community. I would like to create a Data-Driven framework, but do not know which application I can use. But yes, it will show usage of both OR and DP.

  • ejiaj June 9, 2011, 10:53 am

    one excel sheet is there like this
    1 2 3
    4 5 6
    7 8 9

    i want to get o/p like
    1 4 7
    2 5 6
    3 6 9

    … thanks in advance

    Reply
  • Prasad May 16, 2011, 5:57 am

    Hi Anshoo,

    I have been using your framework and i had couple of doubts;
    - Is it possible to merge the interpritor and the implimentor within one single script? i.e -Is it possible to club Descriptive programing and Object Repository in a single script? If yes what could be the approach.
    -Is it possible to switch from Interpritor to implimentor (or Vice Versa) in a script?

    Thanks,
    Prasad.

    Reply
    • Anshoo Arora May 19, 2011, 7:32 pm

      @Prasad: Yes, you can merge both classes into one. This framework currently does not support OR, but it can with a few tweaks – you will have to create a bridge that determines when you use an OR style description as opposed to a DP style. Also, the objects must be present in the OR and associated with the test.

  • narayana April 29, 2011, 7:56 am

    I have used the ‘Nothing’ with the Excel application reference variable, still i got the isssue as “The DataTable.ImportSheet operation failed.
    Cannot open the C:\Users\Sahasra2\Desktop\NARI\d\Testing\insertec\Test Reports\Testcases.Useraccount.Insertec4.xls file because it is already open in another application”
    PLZ HELP ME OUT IN THIS REGARD

    Reply
    • Anshoo Arora May 19, 2011, 7:22 pm

      Narayana,

      You may also have to ‘QUIT’ the application. By setting the instance to ‘Nothing’, you are simply releasing it, not terminating the process.

  • narayana April 29, 2011, 7:53 am

    Hi Anshoo,
    I am new bee to this web site and its damn good one ever seen and i am new to automation testing as well.
    Whenever i tried to execute my script, the excel sheet get locked and i am getting the error as “the respecitve sheet is already open”. How can over come this issue.
    Thanks,
    Narayanarao.T

    Reply
    • Anshoo Arora May 19, 2011, 7:21 pm

      Narayana,

      What may be happening is, you are creating an instance of Excel, and not closing it at the end of the script. Or, you are creating two different instances within the same script and trying to overwrite an open Excel file. Can you share your code here? I may be able to help once I see it.

      Thanks- Anshoo

  • Tony January 19, 2011, 2:36 pm

    Hi…

    Have a question that regaurds passing blank values back to the objects using datasheets. For example, I’ve just recorded all the objects on the page (radio,webedit,weblist…etc) and I parameterize them with datatable. How can I just leave the cells in the datasheet blank(or add “SKIP”) that I don’t care to answer or SKIP. For example row 2 below.

    Browser(“B”).Page(“1″).WebList(“customerBilling”).Select DATATABLE(“Billing”,sheet)
    Browser(“B”).Page(“1″).WebRadioGroup(“useCallerAs”).Select DATATABLE(“Caller”,sheet)
    Browser(“B”).Page(“1″).WebEdit(“timer”).Set DATATABLE(“Timer”,sheet)

    Datasheet:
    Billing | Caller | Timer
    Row1 Cash | Rex | 4
    Row 2 SKIP | SKIP | SKIP

    Reply
  • Ravi January 17, 2011, 12:01 pm

    Hi,
    Thanks for the article and your explanation to each and every topic is excellent.

    I have a question. I have created 2 actions. The Action 3 is called from Action2. In action 3 , i have parametrized using Excel. The Excel sheet has 4 data and all the 4 values are retrieved for two times. can you please help why this happens. below is the script. Can you tell me why, the data are pulled for 2 times?
    Excel Sheet:
    ——————
    AccountNumberN AccountNumberP
    !ӣ$%^& 1234567
    qazw123 1111111
    123qaz 9999999
    858 5858 55555
    ——————————————-
    Dim iRow, sAccountNumber
    CONST iAccountNumberNCol = 1 ‘AccountNumberN is in Column A
    CONST iAccountNumberPCol = 2 ‘AccountNumberP is in Column B
    Set xlApp = CreateObject(“Excel.Application”)
    Set xlBook = xlApp.WorkBooks.Open(“C:\Program Files\Mercury HP\test.xls”)
    Set xlSheet = xlBook.WorkSheets(“Sheet1″)

    iRows = xlSheet.UsedRange.Rows.Count
    iColumns = xlSheet.UsedRange.Columns.Count

    sAccountNumberN = xlSheet.Rows(2).Columns(1).Value
    sAccountNumberP = xlSheet.Rows(2).Columns(2).Value

    For iRow = 2 to xlSheet.UsedRange.Rows.Count
    ‘Retrieve Positive and Negative values for AccountNumber from “iRow” rows and columns A & B
    sAccountNumberN = xlSheet.Rows(iRow).Columns(iAccountNumberNCol).Value
    sAccountNumberP = xlSheet.Rows(iRow).Columns(iAccountNumberPCol).Value

    Browser(“Test”).Page(“Test”).WebEdit(“page2.page2Form.balance_4″).Set sAccountNumberN

    Browser(“Test”).Page(“Test”).WebEdit(“page2.page2Form.balance_4″).Set sAccountNumberP

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

    Reply
  • Ajay January 17, 2011, 3:38 am

    Hi,

    i m tring to invoke https://abc.com

    but not able to set certificate in script..so i need to do something on browser..or smwer to set it on QTP.
    pls help me i am neew to this tool,

    thanks

    Reply
    • Anshoo Arora January 22, 2011, 7:20 am

      Do you mean installing site-certificate on the browser??

    • Anonymous January 24, 2011, 3:43 am

      Yes….how can i installcerts of my https secured application ? i.e public keys of the other application in qtp?

  • arpita January 13, 2011, 2:19 am

    Hi Anshoo
    suppose one page is like that
    when i click on that link it should go to next page.but it’s not identify the link.only it shows that
    JavaWindow(“ty”).JavaTable(“hh”).SelectRow “#0″
    how it identify that link?
    pls reply me

    Thanks

    Reply
  • sukumar January 5, 2011, 8:13 am

    hi anshu i have one question. recently i was asked this question. a data table has 20 rows of data. but we have to get data on 4th 7th and 11 th row only. how can we instruct qtp to do so.

    Reply
    • Anshoo Arora January 10, 2011, 7:34 am

      Sukumar,

      You can use DataTable.SetCurrentRow to focus on the target row. Example:

      DataTable.SetCurrentRow(4)  'sets Row # 4 as the current row
      DataTable.SetCurrentRow(7)  'sets Row # 7 as the current row
      
    • sukumar January 12, 2011, 1:23 am

      its working.thanks for your reply.

  • mini November 8, 2010, 2:49 am

    hiii anshoo,
    Can u explain me configuration management tool with example?

    Thanks

    Reply
  • Jayita October 28, 2010, 5:08 am

    Hi Anshoo,

    Thanks for the excellent information that you share. I am suddenly facing a problem while importing my excel sheet into the datatable.
    The datatable has the first row similar to the column headings.
    My excel workbook RollOffRequest has the following entries in the sheet “BulkUpload” in the first row:
    Name_of_resource No_of_Days

    I am using the following commands to import the BulkUpload sheet
    strSheetName = Parameter(“SheetName”)

    Datatable.AddSheet strSheetName

    Datatable.ImportSheet GetCurrentPath() & “\DSM_TestData\RollOffRequest.xls”,strSheetName,strSheetName

    Can you tell me what is the cause of this issue?

    Reply
  • Guhan October 19, 2010, 3:57 pm

    Hi Anshoo,

    Excellent piece of information that is going to help me in running the script with data from excel sheet.
    Keep up your good work.
    Thanks a lot.

    Regards,
    Guhan

    Reply
  • Gaurav Anand October 14, 2010, 7:44 am

    Hi Anshu,

    A small question

    In my applicatin this line works fine

    browser(bDesc).page(pDesc).Link(vdesc).highlight

    but using this i can only highlight the Links.Is thr any posibility that i can pass (link,webelement,webbutton) as a parameter and create a single function to verify all of them.

    Something like this

    vals = “Link”
    browser(bDesc).page(pDesc).vals(vdesc).highlight

    Vals= “Webelement”
    browser(bDesc).page(pDesc).vals(vdesc).highlight

    Do reply.

    This looks more active so thaught of posting this here as well

    Thanks

    Reply
  • Deepak October 13, 2010, 1:26 pm

    Hi Anshoo,

    Could you please let me know what is Business Process Testing(BPT)?
    Can you please let me know the usage of it? I will be higly thankful to you, if you provide me some realtime examples or documents from where i can learn more.

    Thanks
    Deepak

    Reply
  • Steve October 13, 2010, 6:56 am

    Hi Anshoo

    Fantastic resource centre, many thanks.

    I found this site as my requirement is to data drive my tests from an external spreadsheet. That way if we want people to supply the test data, they need not have to access QTP to do so.

    We are using QC for version control and I wonder whether I need to add the Excel document in QC as well, just in case the copy on the shared folder is corrupted/deleted/amended in error etc. I looked into this and wasnt sure how I created a link between the Excel doc and the actual Test. To be honest I am not even sure if this si a good idea or not, so would appreciate your thoughts on this.

    Thanks

    Steve

    Reply
    • Vijju October 14, 2010, 12:22 am

      Hi Steve,

      It would be a good idea to store the Excel files to QC, but if the file sizes are too long, it may take up lots of space and may affect the performance of QC. For the other question of Version Control, i think QC may not be the version control like VSS or SubVersion. In QC it just changes the version number each time you make any change but i don’t think you can go back to previous versions when you really want it once the code is modified. I couldn’t find that option anywhere in QC. I would rather prefer SubVersion.

    • Anshoo Arora November 14, 2010, 5:08 pm

      Vijju,

      You’re correct. We went back and forth with HP with the way version control has been implemented in QC. The increments are in Integer format – 1, 2, 3, 4.. We also wanted major and minor versions but that’s not possible (yet).

    • Anshoo Arora November 14, 2010, 5:07 pm

      Steve,

      We’re also moving towards version control in my current project and as our framework reaches the next version, so do all our components: libraries, tests, data, recovery scenarios. We’re also using Excel as our data-source and its associated with the same version as the rest of our framework. So, as our framework’s version is incremented, all the resources are moved to the new version..

  • $enthil October 10, 2010, 3:30 pm

    Hi
    Kindly reply for My question below….
    For example, Am having 3 rows for (Login Name and password) in Global sheet , LIke
    Login Name | Password
    1)JoJo mercury
    2)abc mercury
    3)abcd mercury
    The criteria for “Pass” in the “Login name” Should have 4chars length, but here in above data (2)nd row consists of three chars and error will be raised and can’t continue to proceed next step … i need to override this error message and move to 3)rd record continuously without stopping.

    Reply
    • Vijju October 14, 2010, 12:25 am

      Negative testing may not be a good idea for Automation testing.

    • Anshoo Arora November 14, 2010, 5:04 pm

      Considering you’re using the DataTable:

      If Not Len(DataTable("LoginName", dtGlobalSheet)) >= 4 Then
          'code and logic to move to the next iteration
      End If
      
  • Archishu October 1, 2010, 1:27 am

    Anshoo,

    How to compare R1C1 with R1C3, R1C2 with R2C3, R1C3 with R4C3, R2C1 with R3C3 etc…

    Excel1.xls :
    C1 C2 C3 C4
    R1
    R2
    R3
    R4

    Excel2.xls
    C3
    R1
    R2
    R3
    R4

    Thanks in advance

    Reply
    • linh October 6, 2010, 9:37 pm

      =EXACT(A1, C3)
      =EXACT(A2, C2)

      This might help

    • Archishu October 17, 2010, 9:28 pm

      Hi All,

      How to convert this excel formula into VBScript

      =EXACT(A1, C3)
      =EXACT(A2, C2)

      Thanks

    • Vijju October 18, 2010, 12:12 am

      ‘Try This. Please Leave a comment, I’ve not tested this
      Dim Exl_Obj
      Set Exl_Obj = CreateObject(“Excel.Application”)
      Exl_Obj.visible = True
      Set WB_Obj_1 = Exl_Obj.Workbooks.Open(“C:\Excel1.xls”)
      Set WB_Obj_2 = Exl_Obj.Workbooks.Open(“C:\Excel2.xls”)
      Set WS_Obj_1 = WB_Obj_1.Worksheets(1)
      Set WS_Obj_2 = WB_Obj_2.Worksheets(1)
      If WB_obj_1.Cells(1, 1).Value WB_obj_2.Cells(1, 3).Value OR WB_obj_1.Cells(1, 2).Value WB_obj_2.Cells(2, 3).Value
      cell.Interior.ColorIndex = 3
      Else
      cell.Interior.ColorIndex = 0
      End if
      EXl_Obj.workbooks(“Excel1.xls”).save
      EXl_Obj.workbooks(“Excel1.xls”).Close
      EXl_Obj.workbooks(“Excel2.xls”).save
      EXl_Obj.workbooks(“Excel2.xls”).Close
      Exl_Obj.Application.Quit
      Set Exl_Obj = nothing

    • Archishu October 18, 2010, 4:49 am

      Thanks Vijju I modified the above code the below code is working but it needs some tweaking because it colors all the cells rather than cells with difference, i think “cell.Interior.ColorIndex = 5″ needs to be changed. Anshu Would you tweak this???? Thanks in advance
      Dim Exl_Obj
      Set Exl_Obj = CreateObject(“Excel.Application”)
      Exl_Obj.Visible = True
      Set WB_Obj_1 = Exl_Obj.Workbooks.Open(“D:\1.xls”)
      Set WB_Obj_2 = Exl_Obj.Workbooks.Open(“D:\2.xls”)
      Set WS_Obj_1 = WB_Obj_1.Worksheets(1)
      Set WS_Obj_2 = WB_Obj_2.Worksheets(1)
      For Each cell In WS_Obj_1.UsedRange
      If WS_Obj_1.Cells(1, 1).Value WS_Obj_2.Cells(2, 2).Value OR WS_Obj_1.Cells(1, 2).Value WS_Obj_2.Cells(1, 2).Value Then
      cell.Interior.ColorIndex = 5
      Else
      cell.Interior.ColorIndex = 0
      End if
      Next
      Exl_Obj.workbooks(“1.xls”).save
      Exl_Obj.workbooks(“1.xls”).Close
      Exl_Obj.workbooks(“2.xls”).save
      Exl_Obj.workbooks(“2.xls”).Close
      Exl_Obj.Application.Quit
      Set Exl_Obj = nothing

    • Anshoo Arora November 14, 2010, 4:50 pm

      Archishu,

      That will require customization of this class, or creating code from the scratch..

  • Adwitha September 15, 2010, 5:38 am

    Hi Anshoo,

    We have application completely built on Excel (macros). It is not a web app / not a windows app. First 15 rows were reserved for design. below that around 30 rows and 20 columns. those cells were filled with data (numbers), which will change when some cells are changed. This looks like a bit complicated for automation with no objects and pure excel apps. Is there a way we can validate huge list of values filled in the cells when one of the cell changes??? This is the first time i have seen a application like this. I really appreciate ur help in this.

    Thanks in advance

    Reply
    • Anshoo Arora September 29, 2010, 2:51 pm

      Adwitha,

      You can verify any change within Excel even in a given region, but it will be done within Excel using VBA and not through QTP..

  • Kumar September 13, 2010, 6:59 am

    URL=”project url”
    Call OpenBrowser(URL)

    Browser(“openedbytestingtool:=True”).Page(“title:=EPAY Authentication”).WebEdit(“name:=.*txtUserId”,”index:=0″).Set datatable(“Username”,dtGlobalsheet)

    A= Browser(“openedbytestingtool:=True”).Page(“title:= “).WebEdit(“name:=.*txtPassword”,”index:=0″).SetSecure datatable(“Password”,dtGlobalsheet)

    B= Browser(“openedbytestingtool:=True”).Page(“title:=”).Image(“name:=ctl00\$BodyHolder1\$btnLogin”).Click
    If I am assigning variable like this I am getting Syntax error.Please correct me with valid syntax
    so that I can do validation for User name and Password
    Thanks

    Reply
    • Anshoo Arora September 29, 2010, 2:45 pm

      Kumar,

      This is correct:

      URL = "project url"
      Call OpenBrowser(URL)
      

      This is incorrect:

      A= Browser(“openedbytestingtool:=True”).Page(“title:= “).WebEdit(“name:=.*txtPassword”,”index:=0″).SetSecure datatable(“Password”,dtGlobalsheet)
      

      The above statement states that the SetSecure method is returning a value, but it is not. The variable ‘A’ will always be empty. Same for the statement below:

      B= Browser(“openedbytestingtool:=True”).Page(“title:=”).Image(“name:=ctl00\$BodyHolder1\$btnLogin”).Click
      
  • mini September 8, 2010, 10:35 am

    Hi Anshoo,
    As you rightly pointed out, I want to see qtp result in .xls format
    Also pls let me know how to get it in .txt format.
    Thanks.

    Reply
    • Anshoo Arora September 8, 2010, 11:29 am

      I don’t think we can achieve it with .XLS, even with QTP 10.0.

      Also, for .txt, the report can be quite hard to read. QTP can create reports in HTML – I would recommend that, or .DOC for that matter.

  • pradeep September 6, 2010, 7:15 am

    Hi Anshoo,
    I am not able to write a script to decimal values from excel sheet,Could u please help me

    Reply
    • Anshoo Arora September 8, 2010, 8:07 am

      Rajendraprasad,

      Not sure if I understand the question? Write a script to decimal values?

    • pradeep September 9, 2010, 7:30 am

      HI Anshoo,
      As you rightly pointed out
      can u please provide script for the decimal values like (20.00 , 20.50,20.75etc).Please let me know.

      Thanks in advance
      Pradeep

  • mini August 30, 2010, 1:13 am

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

    Reply
    • Anshoo Arora September 2, 2010, 7:39 am

      Mini,

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

  • Arpita August 10, 2010, 11:19 am

    Thanks Anshoo
    Your tips really helped me…

    Reply
    • vamsikrishna April 29, 2011, 6:16 am

      Anshoo u r so great

  • Arpita August 9, 2010, 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

    Reply
    • Anshoo Arora August 9, 2010, 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.

  • Anonymous August 6, 2010, 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

    Reply
    • Anshoo Arora August 8, 2010, 7:44 pm

      Try using 5 for the column instead of E. Does that work?

  • arpita August 4, 2010, 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

    Reply
  • arpita August 4, 2010, 3:48 am

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

    Reply
    • Anshoo Arora August 5, 2010, 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
      
  • Deepak August 3, 2010, 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

    Reply
  • Jakka August 3, 2010, 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

    Reply
    • Anshoo Arora August 5, 2010, 2:00 pm

      Jakka,

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

  • sree July 13, 2010, 3:47 pm

    Really Great website

    Reply
  • Al June 29, 2010, 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

    Reply
    • Anshoo Arora July 6, 2010, 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.

  • Rohan June 2, 2010, 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

    Reply
    • Anshoo Arora June 7, 2010, 8:44 am

      Can you please share the function/code that you are using to write to Excel?

  • Rohan May 28, 2010, 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.

    Reply
    • Anshoo Arora June 1, 2010, 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.

  • Sumit patra May 20, 2010, 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 ).

    Reply
    • Anshoo Arora May 26, 2010, 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")
      
  • chopra May 19, 2010, 2:31 pm

    Anshoo, is there way to write a function / procedure for a role based login. What i am wishing is as follows.
    role | Username | Password
    Manager | ABC | XYZ
    Accountant | BCD | XXX
    ..
    ..
    Many roles will be listed

    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.

    Reply
    • Anshoo Arora May 26, 2010, 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:

      Login "Manager"
      Login "Accountant"

    • Chopra May 26, 2010, 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

    • Anshoo Arora June 1, 2010, 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.

  • Rohan May 18, 2010, 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

    Reply
    • Anshoo Arora May 26, 2010, 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
      
    • rohan May 28, 2010, 5:49 am

      Thanks heaps Anshoo

    • vamsikrishna April 29, 2011, 5:29 am

      hi anshoo arora u r providing very gud information on QTP

    • Anshoo Arora May 19, 2011, 7:19 pm

      Thank you Vamsikrishna!

    • Vijay November 21, 2011, 2:24 pm

      Hi Anshoo,

      Need a help from you

      am trying to automating the request forms data and the data fields like in below
      Username :
      mailAddress:
      ID :
      Name :
      Phone Number:
      Account Number :
      Address :

      All the above fields will store in exce and from excel qtp has to get those and fill the sheet automatically
      And in excel the format is like in below
      Username ID Name Phone Number Account Number Address
      smith 5678 Smith 986712352 NA NA
      Zak 2314 ZakS 0294678912 NA NA

  • Rohan May 17, 2010, 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

    Reply
    • Anshoo Arora May 18, 2010, 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?

  • Deepak May 16, 2010, 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

    Reply
    • Anshoo Arora May 18, 2010, 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.

  • Deepak May 12, 2010, 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

    Reply
    • Anshoo Arora May 14, 2010, 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.

  • Deepak May 12, 2010, 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

    Reply
    • Anshoo Arora May 12, 2010, 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?

  • Deepak May 11, 2010, 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

    Reply
    • Anshoo Arora May 12, 2010, 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.

  • Deepak May 10, 2010, 7:33 pm

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

    Reply
  • Deepak May 10, 2010, 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

    Reply
    • Anshoo Arora May 10, 2010, 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
      
  • DEVENDRA SHARMA February 10, 2010, 2:40 pm

    Thanks a lot Anshoo!!!

    Reply
  • DEVENDRA SHARMA February 10, 2010, 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)

    Next

    Regards,
    Devendra Sharma

    Reply
    • Anshoo Arora February 10, 2010, 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).

  • DEVENDRA SHARMA February 10, 2010, 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

    Reply
    • Anshoo Arora February 10, 2010, 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
      
  • DEVENDRA SHARMA February 10, 2010, 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
    
    Reply
  • DEVENDRA SHARMA February 10, 2010, 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

    Reply
    • Anshoo Arora February 10, 2010, 3:04 am

      Devendra,

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

  • DEVENDRA SHARMA February 8, 2010, 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

    Reply
    • Anshoo Arora February 10, 2010, 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.

  • Anonymous February 6, 2010, 12:45 am

    HI Anshu,

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

    Reply
    • Anshoo Arora February 7, 2010, 8:03 am

      Thank you. :)

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

  • !ndra February 6, 2010, 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.

    Reply
    • Anshoo Arora February 7, 2010, 8:01 am

      Hi Indra,

      Thank you. :)

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

  • padma February 5, 2010, 7:12 pm

    what if i need to change the URL every time?

    Reply
    • Anshoo Arora February 7, 2010, 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