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:
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:
- Right-click on the Action name in the Tree View.
- 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!
Hi Ankur I am facing general run time error when I export the datasheet from the datatable in uft 12.02 . Using the keyword Datatable.exportsheet …. Scenario is I have imported the Excel which has formulas. Kindly let me know how to read the cell value which has formulas as well from the datatable
I need to import one xls file to windows application and please give the code.
Hi Ankur,
I have my input data in one column and expected value in another column but problem is we are not verifying the data for every input. so there is mismatch in rows of input data and expected value.e.g. I am taking input from C2, but verifying the expected result in D4.
We could define it with direct row and column number but I have more than 200 such input files, so defining row, column number will make script very heavy. could you suggest any solution to handle it dynamically.
Regards,
Santosh
@Santosh: For anything dynamic, the rule-of-thumb is to see a pattern. As long as you can visualize a pattern to your expected value cells, it becomes a matter of few lines of code.
how to retrieve data from all the college’s email id and paste in excel sheet using qtp http://www.indiacollegesearch.com/
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?
Without seeing your code, it’s difficult to say anything. Please ask your question on forums.
Hi ,
I am using Web Application and setting is also done then while running my script then it shows windows objects.
Please help me in this.
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”
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.
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
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
Hi could you please guide how to retreive the different browser from excel
thanks
Murali
Murali3d@gmail.com
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
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
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
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?
This is really helpful, very straight forward for me. Keep up the good work!
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
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
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.
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
Hi All,
Can we use excel as database in QTp?
@supriya : you can go for Qspider , Bangalore . I think this is the best institute for learning QTP.
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
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
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
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
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
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
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
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
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.
Hi Ankur,
Does QTP 9 support audio/video streaming?
Thank You,
Luky
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?
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)
hey ankur i m not able to record the dialog boxes getting opened after right clicks of mouse how 2 fix this bug
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?
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…
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
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
Examples in the site are Great and simple to understand.
Great ! Keep it UP
@ 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.
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
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
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
Hey Ankur
How can we retrieve data from a notepad file?
Hi How can we retrieve the data present in an excelsheet using VBScript with QTP.
Kindly help me out
@ 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?
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???
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?
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
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