Use test data directly from external sheet

by | Last Updated On Jul 13, 2014 | QTP and Excel | 30 comments

In some of our earlier posts we have seen How to work on QTP datatable, How to import/export datatable, what are the different functions available for datatable and also the basics of QTP Automation Object Model(AOM).
Here we will combine AOM and Excel automation model to import data from an external excel file to QTP without actually importing the file as a ‘datatable’ and use test data directly from external sheet.

The workflow for the process would be:

  1. Create an excel object.
  2. Create a workbook object that takes file path as input.
  3. Create a sheet name object that takes sheet name as input.
  4. Perform operations on sheet.
    1. …………………………………
    2. …………………………………
    3. …………………………………
  5. Quit excel application.
  6. Destroy objects defined above.

We will show the whole process with an example. We will create a function that will accept file path and sheet name as input and will give the output an array which will contain all the values of column 1 of the input sheet.

Function ParamValues(InputFilePath,SheetName)

Dim input()
Set appExcel = CreateObject("Excel.Application") 'Step 1
Set objWorkBook = appExcel.Workbooks.Open (InputFilePath) 'Step 2
Set objSheet = appExcel.Sheets(SheetName) 'Step 3

For i= 2 to 50 'Step 4
if objSheet.cells(i,1).value <> "" then
Input(i-2)=Trim(objSheet.cells(i,1).value)
else
Exit For
End If
Next

appExcel.quit 'Step 5
Set appExcel=Nothing 'Step 6
Set objSheet=Nothing
Set objWorkBook=Nothing

End Function

InputFilePath and SheetName are the input arguments passed to the function named ParamValue. The output of the above function will be an Input() array that will contain all the values of column 1 of the given excel sheet.
You can call the above function by: ParamValues <Your File Path>, <Sheet Name>

Copy the above code and test it by yourself to have more clarity.

You Should Read These 3 Related Articles Too!

30 Comments

  1. Lilian on Mar 18, 2015 at 22:16

    Hi,

    How can I use data from excel file working with WebElement?

    Like, I have a excel file and I want to read this file and from this, find the data on the WebElement (combobox).

    Reply
  2. Mahendra on Feb 14, 2015 at 14:21

    i have Open Office in my PC, how to edit the datasheet of open office in QTP?

    Reply
  3. Santhosh on Sep 22, 2014 at 17:46

    Hi,

    I would like to export the data from access database table to excel.
    please let me know how can i do this using QTP tool?

    Reply
  4. Meenakshi on Nov 12, 2013 at 11:33

    Hi Ankur,

    What is the difference between utilizing the datatable(exporting and importing )& direct Excel files for data. I feel comfortable with Excel obj model only. But I don’t know the actual reason.

    Reply
  5. Vaishali on Sep 11, 2012 at 10:56

    Hello,
    I want to write on function in which I wll be reading some cell data from excel and adding thta data into peoplesoft application.I have to handle 3 iterations like for frist row user will login add data from first row excel and logout..then again login add data from second row excel and logout and same fro third row.Could you pelase help me out in this…

    Reply
  6. kinal on Apr 23, 2012 at 23:07

    Hi Saurabh
    i have copyied the folder and made changes as you said in properties and security for the folder but still it is giving me the same error file is ready only or encrupted .
    Do you have any idea why it is doing so

    Thanks

    Reply
  7. Prasad on Mar 20, 2012 at 15:59

    Hi Ankur,

    Instead of EXCEL, if I am using Open Document spreadsheet (openoffice.org); How do I go about reading data from such a spreadsheet.

    Reply
  8. Bandi sekhara reddy on Mar 20, 2012 at 13:37

    Ramesh,
    By using AddSheet method you can add sheet to Run time datatable ..

    Reply
  9. Bandi sekhara reddy on Feb 28, 2012 at 17:15

    HI All,
    How to work with pdf files with QTP

    Reply
  10. ramesh on Feb 26, 2012 at 17:12

    did any one know how to add new sheet to excel at run time in qtp

    Reply
  11. sukanya on Feb 20, 2012 at 17:02

    Hi all,

    can any one tell me how to take data from excel and run through QTP.

    Regards,
    Sukanya

    Reply
  12. shan on Dec 28, 2011 at 18:20

    how i can print values in the array

    Reply
  13. Saurabh on Nov 25, 2011 at 16:02

    Hi Kashif,

    I too had same problem, bur i solved it.. here is procedure
    Create a new folder in C: for eg c:\test -> copy your xlsx file in this folder right click on folder open properties, uncheck readonly, and in security settings, give full control to group named “users”..Now it will work as it worked for me.

    This is a noraml problem when you are not logged in with administrator privilages.

    Cheers
    Saurabh

    Reply
  14. rishi on Nov 10, 2011 at 16:43

    Hi, If we don’t import the Sheet, how do we get that sheet in the Test Result?

    Reply
  15. saranya on Sep 28, 2011 at 14:49

    Hi All, in this time i need your support.
    here is my question.
    clicking an image(which generated a excel sheet)-> i want to open the file -> check the excel sheet info with the data table info.
    how i should write scripts for this functionality..?
    Thanks,
    Saranya

    Reply
  16. abby on Jul 7, 2011 at 14:49

    Please check
    this line
    if objSheet.cells(i,1).value “” then
    should be
    if appExcel.cells(i,1).value “” then

    Reply
  17. Lokesh on May 24, 2011 at 15:48

    I can import excel sheet in qtp scripts but not able to read multiple records from excelsheet in qtp script.
    Please help me in doing all that task ….

    Reply
  18. Kodak on Apr 7, 2011 at 20:59

    Does QTP 9.2 supports “Excel Automation Model”?
    I tried this on v9.2 but could not get correct result. Please suggest.

    Reply
  19. srinivas reddy on Aug 22, 2010 at 11:44

    how canwe handle erros other than using recovery scenerios in qtp

    Reply
  20. ankur mishra on Jul 20, 2010 at 19:00

    hi ankur,
    i am working with qtp ver.10
    can you please tell me how to capture objects from an .asp page.
    and is there a .asp add in for qtp????

    thanks and regards

    Reply
  21. Ram on Jul 11, 2010 at 20:53

    Is it possible to access the QTP Datatable in vb.net?

    Reply
  22. rasheed on May 24, 2010 at 19:14

    hi guys,

    can you please tell me how to read the 3rd row and 4th column data from xl sheet without using data table?

    Thanks & regards

    Rasheed

    Reply
  23. vishal on Mar 15, 2010 at 15:42

    can we associate multiple test data files with single QTP script?
    please advice !

    Reply
  24. Anuj on Mar 12, 2010 at 04:09

    Hi,

    I am doing some POC for automation on a .NET + WPF application. Though I am able to see the object’s properties when I am spying on the screen but not able to perform following:
    1. When I am trying to record some basic actions on a screen, no recording is happening.
    2. When I try to learn the objects from Object Repository Manager, it shows as if it is learning the objects and shows the message “Analyzing objects…” but no object is added to the repository.

    Please suggest some resolution.

    Thanks
    Anuj

    Reply
  25. Vijay on Jan 27, 2010 at 16:20

    Hi Ankur,

    Can you please give information on how to connect to a SQL/Oracle database using QTP?
    I am facing a problem related to this. I have QTP installed on CITRIX and every time i open it, it gets opened in a different server. now i am using TOAD application to connect to my database, but this application is installed on the different server than QTP. Is it possible to connect to database server (on a different server) using QTP?

    Your help in this regard is highly appreciable. Thanks!!!

    Reply
  26. Kashif on Jan 19, 2010 at 14:26

    Hi Ankur,

    I have a problem running the code. Am getting an error in step2 stating “Excel cannot access “My Documents”.The document may be read-only or encrypted.” I even tried to change the location of the test input excel but same error..the test input file is not encrypted as well.

    Let me know what can be possible cause of this error.

    Thanks,
    Kashif

    Reply
  27. subbareddy on Jan 8, 2010 at 23:15

    Hi,
    Instead of importing the sheet, the data is loaded into a variable INPPUT , so you are using the lotof memory. otherway, create an ADODB object and then read the data using SQL as required for the AUT’s page.

    Reply
  28. Sumi on Mar 5, 2009 at 11:51

    Hello,

    I’ve a question in regards to the above piece of code. What if I use UsedRange function to get all the rows with some data?

    In that case, how can I use the for loop to go through the excel sheet?

    Also how can I use an array to collect data when looping through required rows in the external excel sheet (not the datatable).

    Thanks in advance for all the help.

    Reply
  29. mintu on Feb 10, 2009 at 20:55

    Hi ,

    This concept has really helped me in increasing the performance of my scripts as the time used to load the data in the script is reduced,
    Thanks..

    Reply

Submit a Comment

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