QTP and Excel Part1

Updated On Jul 8, 2014 by

After our series on VB Script and QTP Part1, Part2, Part3 I would like to post a series on integration of QTP with MS Excel. Basically this series is to make the readers aware of how to work on QTP using data table and external excel sheets.

How can we use the data table to provide input data to an application?

Use the DataTable.Value method to access data from the data table and input it into the application
For data in the Global datasheet:QTP and Excel
1. Open a new script.
2. In column A of the Global datasheet, enter the data in three rows.
3. Go to www.google.com.
4. Begin recording.
5. Type a value into the Google search field.
6. Stop recording.
7. Go to the Expert view. Modify the script so it look like this:

rc = DataTable.Value ("A", dtGlobalSheet)
msgbox rc
Browser("Google").Page("Google").WebEdit("q").Set rc

8. To run all rows in the global data table, go to Test ->; Test Settings -> Run tab, and select “Run on all rows.”
For data in the Local datasheet:
1. Start a new script.
2. In column A of the Action1 datasheet, enter the data in three rows:
3. Go to www.google.com.
4. Begin recording.
5. Type a value into the Google search field.
6. Stop recording.
7. Go to the Expert view. Modify the script so it look like this:

rc = DataTable.Value ("A", dtLocalSheet)
msgbox rc
Browser("Google").Page("Google").WebEdit("q").Set rc

8. To run all rows:

  1. Right-click on the Action name in the Tree View.
  2. Go to Action Properites -> Run tab, and select “Run all rows.”

Similarly, How can we use the data table to get output data from an application?

Create an Output Value. The text will be placed in the datatable and can be accessed as needed.
1. Once you see the text you want to retrieve, start recording.
2. From the Insert menu, select Output Value, then Text Output Value.
3. Click on the desired text. The “Text Output Value Properties” window will appear.
4. In the window you can verify or set the Before and After text settings.
5. By default the retrieved value will be added to the Global sheet. You can modify the settings by selecting Output Text in the combobox, then clicking Modify.
6. Once satisfied, click OK.
An Output statement will be entered into the script.

Browser("Browser").Page("Page").Output CheckPoint("Text")
msgbox DataTable.Value("PageOutput_Text_out", dtGlobalSheet)

In addition, a column (in the example, PageOutput_Text_out) will be inserted into the datatable(Remember in the runtime datatable), with the output text.
OR Another method to retrieve data during run time is to do just the opposite of what we did above in the first question above.
DataTable.Value(ParameterID [, SheetID])=NewValue
The value property is the default property for the DataTable object. As the default property you do not need to explicitly use .Value.
DataTable(ParameterID [, SheetID]) = NewValue

' Add data to the current row of the Global sheet
DataTable("VarName", dtGlobalSheet) = "new value" ' Using DataTable by itself
DataTable.Value("VarName2", dtGlobalSheet) = "new value2" ' Using .Value
' Add data to the current row of the Local sheet
DataTable("VarName", dtLocalSheet) = "new value" ' Using DataTable by itself
DataTable.Value("VarName2", dtLocalSheet) = "new value2" ' Using .Value

If you want to keep track of further articles on QTP. I recommend you to subscribe via RSS Feed. You can also subscribe by Email and have new QTP articles sent directly to your inbox.

Please use the QTP forum for posting questions now!

Please use QTP forum for posting QTP questions.

Welcome new readers: Download the Free eBook!

Ankur JainI am Ankur Jain, the owner of LearnQTP and founder of a software organization. This site is run, maintained and updated by me and my team. Feel free to take a look around, you may want to start with What is QTP? 

You can connect with me on my Google+ page. 

Please enter your first name and email address below to instantly download the eBook on Optimizing QTP Scripts. In future, we will make sure you get new tips & tricks on QTP delivered direct to your email box.

Share This:


  1. Anand

    how to retrieve data from all the college’s email id and paste in excel sheet using qtp http://www.indiacollegesearch.com/

  2. vijay


    i have imported the data sheet in the beginning of the script.
    When i run the script from starting, i am not able to set value for a text field using data sheet

    instead, if i add the importing code in between just above the concerned line where i set the data in the text field, and run the script from that step. its working. Dont know whats happening?

    • Ankur Jain

      Without seeing your code, it’s difficult to say anything. Please ask your question on forums.

  3. kapil patil

    I need help on my below code. I have an excel sheet which gives signal by computing data from live application. I want qtp to run actions or wait as per signal generated by excel; but as I connect or start entering data in excel, it gives error. It shows general error. QTP does not allow me to access any excel workbook on my computer during runtiime.
    Please give your suggestions on my below code

    Set Objexcel = GetObject(“”,”Excel.Application”)
    Set Objworkbook = GetObject(“C:\Documents and Settings\K\Desktop\final.xls”)
    Set Objworksheet = Objworkbook.Worksheets(“enter”)

    status=Objworksheet.Cells(1, 1).Value
    If status=”run1″ Then
    msgbox (“run1″)
    Elseif status=”run2″ Then
    msgbox (“run2″)

    End If
    loop while status=”wait”

    • Anand Dutt Pandey

      1st line of code will always give general run error because your are leaving the first argument and trying to get the object of general excel component, this error will appear only when you save/Run test but this can be compressed with on Error resume next operations.

      To avoid this error complety please save the excel and then give the complete path of excel under first argument of getObject method.

      I hope by this your problem might get resolved.

  4. Nandi

    Hi Ankur,
    Could you please tell me how to write script for when i enter Keyboard key is “Enter” then i want display messsage as “You enter ENTER key working Properly,if not working ENTER key message should be displyed as “ENTER” key is not working.
    Pls send me script to nandi.testing@gmail.com

  5. Suband

    Hi Ankur,

    I am trying to solve an issue where webtable has different total number of columns in header than rest of the table rows. Is there a generic way to handle this

    example column header has a column called “Price and Change” while the data rows have values such as $100 and 10% for the same column. The number of columns in each row are different

  6. Murali Gowda

    Hi could you please guide how to retreive the different browser from excel


  7. Anonymous

    Hi, i´m triying to use this function from Excel in the QTp DataTable but it seems that it´s not support “=+’C:\file\[book.xls]Sheet’!$A$4″

    Is to read a value from another excel book.

    When i imported the excel to QTP delete the part of the external file “C:\file\”

    Do you know if theres a way to do this to make a refrence to a value in a external book


  8. Abz


    I want to Fill an application form for a web based application.

    My script is like:
    Browser(“”).Page(“”).WebEdit(“abc”).Set “123”
    Browser(“”).Page(“”).WebEdit(“abcd”).Set “1234”

    I want to parameterize this 123 value and want to import values from an excel in my C drive.

    Could you please provide me a sample code for this.

    Note: I need to pick values from columns as it will be for single iteration.
    Help appreciated.

  9. sai

    Can u plse help me ,how to take data from excel file and execute then i am having error in Flight reservation example ,can u help me……..

    Dim xl,wb,ws
    Set xl=CreateObject(“Excel.Application”)
    Set wb=xl.Workbooks.Open(“f:saiexample.xls”)
    Set ws=wb.Worksheets

    For i=2 to n

    Window(“Flight Reservation”).WinMenu(“Menu”).Select “File;New Order”
    Window(“Flight Reservation”).ActiveX(“MaskEdBox”).Type ws.cells(i,1)
    Window(“Flight Reservation”).WinComboBox(“Fly From:”).Select ws.cells(i,2)
    Window(“Flight Reservation”).WinComboBox(“Fly To:”).Select ws.cells(i,3)
    Window(“Flight Reservation”).WinButton(“FLIGHT”).Click
    Window(“Flight Reservation”).Dialog(“Flights Table”).WinButton(“OK”).Click
    Window(“Flight Reservation”).WinEdit(“Name:”).Set ws.Cells(i,4)
    Window(“Flight Reservation”).WinButton(“Insert Order”).Click
    print i
    Set xl=nothing

  10. Anonymous

    Hi Ankur, I am using QTP 10.0, I am creating custom reports in Excel. Could you kindly let me know how to persist the Runtime DataTable with the results as passed or failed in the excel sheet?

  11. Anonymous

    This is really helpful, very straight forward for me. Keep up the good work!

  12. Bob

    I would like a checkpoint in my script to compare an image (or an area of an image) against a numerical value. For instance, I would like to my script to pull various “bank routing numbers” from my data table and then query our db for the image, and verify the image that is returned is for that routing number. i.e., if I use a BOA routing number, then I want the checkpoint to verify that a BOA check image is retured and not a BONY or TD BANK image. Is it possible to compare an image against a value?
    Thanks – Bob

  13. deepti gupta

    Hi Ankur,
    I am new to QTP and don’t know VB scripting.
    Is there any other way to automate test scripts?
    Can you share some examples and documents please.
    I am into a new job and it requires me to automate tests.

    Pls do help.You can mail me on:

  14. Anonymous


    I have a quick question. How do we get the selected item in HTML ListBox/ComboBox in QTP. I mean, I want to return the item selected in the ListBox in HTML web page and use that in QTP script.

  15. Anonymous

    HI ankur
    could you tell me how to save my data in excel sheet…
    i am using datatable.value(“A”,”action1″)=”pass”

    but after running script see me in run time in data table but when i try to see that my data save on not in excel then i got no value save in excel.
    plz tell how to save my data in excel sheet..
    plz do me solution on prashantkit@gmail.com

  16. Anonymous

    Hi All,

    Can we use excel as database in QTp?

  17. pintu

    @supriya : you can go for Qspider , Bangalore . I think this is the best institute for learning QTP.

  18. Anonymous


    i have functions say fun1(),fun2(),fun3()
    i want to call these functilns from excel sheet.and results also in excel sheet please help me its urgent

    Thanks & Regards

  19. Anonymous

    Hi Ankur,

    What does it mean to “click” on the text that you want to be saved in the run time DataTable? Suppose I search for 12+12= in Google; I end up getting the result. Now, I would like to save the result in my dataTable. But the problem is how do I specify the text for the text output checkpoint? I mean clicking on the result does not do anything; My message box pops up a blank value. Please help.
    Thanks in anticipation,

  20. Anonymous

    How are we save external data in datatable sheet.

    i tried following statement with loop

    but values doesn’t save into column

    please advice.

    is there another way i need to implement.


  21. Anonymous

    hi, can anyone help to install qtp, as i want to learn it, and send me some links from where i can download it .


  22. tej

    Hi ankur i am a fresher to the domain
    of software testing i have lots of challanges to full fill, i want to play with QTP please show a path
    cause i am not finding anyone as such


  23. Anonymous

    Hi i am supriya can anyone tell me which is the best institute to learn QTP automation tool in Bangalore(India).. Please reply to my email id supriysahen@gmail.com..

    Send me QTP Faq & Objective questions related to it.. I request anyone to reply to my email id–> supriysahen@gmail.com

  24. Milan

    Hi Ankur,

    Does QTP support SAP BW 7.0
    If yes any link or document for the same

    Thank You,

  25. Anonymous

    Hi Ankur,

    we r trying to get the sum of price of orders(in flight application of QTP)using data table.
    i=Window(“text:=Flight Reservation).WinEdit(“attached text:=Total:”).GetVisibleText
    msgbox i
    and further we r unable write the script, can u help us? and send me the answer 2 rajendra_js@yahoo.co.in

  26. Shashi

    hi when ever i do the example of global sheet using google.com i get this error : The “q” object was not found in the Object Repository.
    Check the Object Repository to confirm that the object exists or to find the correct name for the object.
    so please give me the solution for this.

  27. Luckshmi

    Hi Ankur,

    Does QTP 9 support audio/video streaming?

    Thank You,

  28. chetan

    This is also great initiative, it helps me a lot.
    I have a que: If I want to fetch value from datasheet based on particular column-row intersection (like where clause in database).
    Like I have value in two column as follows:

    If I want to fetch Capital based on State like in data base:
    Select Capital from tble where state=”Karnatka”

    Is it possible from datasheet, if yes how?

  29. Anonymous


    we have newly started recording scripts for regression in our test application. My question is , I have 10 text boxes in one screen , I need to create a record for each text box with each different data. how can I do this.
    1st record –text box1- xxx(save)
    2nd record—-text box2 -yyy(save)
    3 rd record–text box3–zzz(save)

  30. roh

    hey ankur i m not able to record the dialog boxes getting opened after right clicks of mouse how 2 fix this bug

  31. Lakshmy


    While outputting values, is there a way to import the values to the local data sheet from the run time data sheet? Also, in the data sheet, how can we output values in to different rows?

  32. Anonymous

    I wanted to know if there is a way to take data from multiple rows in one iteration. Alo if more than one datasheet can be used…

  33. Anonymous

    Hi All,

    I wanted to know if we can use two different dataSheet in the same script. If Yes then some one can u please help me knowing how to do the same… Thanks in advance…


  34. Sai

    Thanks Ankur, that helped. I was not looking at Run Time table before. I have another question. Can I pass a variable in the sql query when creating a DB Checkpoint.

    This is what I want to do:

    In the DB Checkpoint query:
    select * from tablename where col=textvalue

    Instead of :
    select * from tablename where col=1234


  35. Naraindrakumar ( naraindran@gmail.com)

    Examples in the site are Great and simple to understand.
    Great ! Keep it UP

  36. @ Sai: As I have written in the post above, the value could be seen in run time data table(ie the one which you get in the results window after your script is executed) not the design time data table.

  37. sai

    Hi Ankur,
    I used your example to add data to the Global sheet using

    DataTable.Value(“XXX”, dtGlobalSheet) = “textValue”

    where XXX was the column name in the Global Sheet and textValue is my value to be inserted. What could be the problem. Nothing is getting written into XXX column.


  38. raju

    hi ankur this is raju . pls help me about this Quality center . if possiable can i have any help regarding qc. pls tell me how to play with qtp.. any links ,pls helpme iam in job search

    waiting fr your positive response

  39. Bharat

    Hi Ankur…

    Thanks for ur guidance….

    just want to add one thing..in QTP 8.2 if u want to run all row….

    To run all rows:

    Right-click on the Action name in the Tree View.
    Go to Action Call Properites (Not Action Properites)-> Run tab, and select “Run all rows.”…


  40. Anonymous

    Hey Ankur

    How can we retrieve data from a notepad file?

  41. Sastri

    Hi How can we retrieve the data present in an excelsheet using VBScript with QTP.
    Kindly help me out

  42. @ Neeraj, Are you sure you need 256 columns? thats too much…How much time will it take to enter data inside those? If your app really requires 256 columns to be filled. Is it really worth automating?

  43. Neeraj

    I have one excel sheet and column is more than 256.So is there any solution to convert this excel sheet in another format and how???

  44. Anonymous

    Hi Anukar,

    I added an excelsheet With the Datatable.addsheet method. I tried to put a value into the added sheet with Datable.value(“a”,”MySheet”) method. But Itz not working/allowing. I am using qtp 8.2. Watz the solution for dis?

  45. arpitha

    HI Ankur,

    In our testing, we are using Mercury Quality Center to run QTP Scripts. I want to get a consolidated report of all the scripts passed/failed and if failed where they are failed. How to generate this report. Please let me know,


  46. Anonymous

    Hi ankur i am a fresher to the domain
    of software testing i have lots of challanges to full fill, i want to play with QTP please show a path
    cause i am not finding anyone as such


    my contact e mail is rocketspeed_rocketspeed@yahoo.co.in

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>