←

→

Use test data directly from external sheet

QTP and Excel

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

   1: Function ParamValues(InputFilePath,SheetName)


2: Dim input()

3: Set appExcel = CreateObject(“Excel.Application”) ‘Step 1
4: Set objWorkBook = appExcel.Workbooks.Open (InputFilePath) ‘Step 2
5: Set objSheet = appExcel.Sheets(SheetName) ‘Step 3

6: For i=2 to 50 ‘Step 4
7: if objSheet.cells(i,1).value <> “” then
8: Input(i-2)=Trim(objSheet.cells(i,1).value)
9: else
10: Exit For
11: End If
14: Next

15: appExcel.quit ‘Step 5
16: Set appExcel=Nothing ‘Step 6
17: Set objSheet=Nothing
18: Set objWorkBook=Nothing

19: 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.

QTP PDFHave you downloaded the FREE Optimizing QTP eBook yet? Get It Now!

QTP RSSI'm sure you have already subscribed to our feed but just in case not. I recommend you to subscribe via RSS feed. You can also subscribe by Email and have new QTP articles sent directly to your inbox.

20 comments ↓

#1 Anonymous on 10.10.08 at 00:26

interesting…

But to Create or Manage external Data, People still stugle.

Here is a link which might give you some more idea of Test Data management: http://www.grid-tools.com/demos/Managing_Capture_replay_data.htm

http://www.grid-tools.com/datamaker-demo.php

#2 mintu on 02.10.09 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..

#3 Sumi on 03.05.09 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.

#4 All About Windows Management Instrumentation (WMI) in QTP | Learn QTP on 09.08.09 at 10:59

[...] software components –under Windows- to interact with each other. QTP’s Automation Object Model, Excel automation model comes under the broad heading of [...]

#5 subbareddy on 01.08.10 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.

#6 Kashif on 01.19.10 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

#7 Vijay on 01.27.10 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!!!

#8 Anuj on 03.12.10 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

#9 vishal on 03.15.10 at 15:42

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

#10 rasheed on 05.24.10 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

#11 Ram on 07.11.10 at 20:53

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

#12 ankur mishra on 07.20.10 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

#13 srinivas reddy on 08.22.10 at 11:44

how canwe handle erros other than using recovery scenerios in qtp

#14 Kodak on 04.07.11 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.

#15 Lokesh on 05.24.11 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 ….

#16 abby on 07.07.11 at 14:49

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

#17 saranya on 09.28.11 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

#18 rishi on 11.10.11 at 16:43

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

#19 Saurabh on 11.25.11 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

#20 shan on 12.28.11 at 18:20

how i can print values in the array

Leave a Comment