QTP and Excel Part1

Updated On Mar 26, 2014 by Ankur Jain

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.
Example:
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
Note:
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
Example:
‘ 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 Article:

47 Comments

  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

    Hi

    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

    Do
    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”

  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

    thanks
    Murali
    Murali3d@gmail.com

  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

    Tnks

  8. Abz

    Hi,

    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.
    Thanks,
    Abhi

  9. sai

    Hi,
    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
    n=ws.usedrange.rows.count

    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
    Next
    wb.Close
    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:
    talk2deeptigupta@gmail.com

  14. Anonymous

    Hi,

    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

    Hi

    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
    vanu

  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,
    Sakura

  20. Anonymous

    How are we save external data in datatable sheet.

    i tried following statement with loop

    datatable.values(“A”,dtlocalsheet)=rs.fields(1)
    but values doesn’t save into column

    please advice.

    is there another way i need to implement.

    -Achyut

  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 .

    vikram_mukhyan@yahoo.co.in

  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

    cheers
    tejmail@yahoo.com

  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,
    Milan
    Milan_Kothari@satyam.com

  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,
    Luky

  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:
    Gujarat-Gandhinagar
    Maharastra-Mumbai
    Karnatka-Bangaore

    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

    Hi,

    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.
    like
    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

    Hi,

    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

    Hi,
    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…

    Raghu

  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:
    textvalue=Browser(“Browser”).Page(“Page1″).WebEdit(“WebEdit”).GetROProperty(“innertext”)

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

    Instead of :
    select * from tablename where col=1234

    Thanks,
    Sai

  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.

    thanks

  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
    cheers
    raju

  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.”…

    Thanks
    Bharat

  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,

    Thanks,
    Arpitha
    argarapati@deloitte.com

  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

    cheers

    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=""> <strike> <strong>