QTP test data directly from external sheet

In a quantity of our earlier posts they have seen How to work on QTP datatable, How to import/export datatable, what are the different functions obtainable for datatable & also the basics of QTP Automation Object Model(AOM).

Here they will merge AOM & Excel automation model to import information from an external excel file to QTP without actually importing the file as a ‘datatable’.

The workflow for the method would be:

1. Generate an excel object.

2. Generate a workbook object that takes file path as input.

3. Generate 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. It will generate a function that accepts the file path and name of the entry sheet and give the output of an array containing all values of column 1 of the entry sheet.

1. 1: Function ParamValues(InputFilePath,SheetName)
2.  
3. 2: Dim input()
4.  
5. 3: Set appExcel = CreateObject(“Excel.Application”) ‘Step 1
6. 4: Set objWorkBook = appExcel.Workbooks.Open (InputFilePath) ‘Step 2
7. 5: Set objSheet = appExcel.Sheets(SheetName) ‘Step 3
8.  
9. 6: For i=2 to 50 ‘Step 4
10. 7: if objSheet.cells(i,1).value <> “” then
11. 8: Input(i-2)=Trim(objSheet.cells(i,1).value)
12. 9: else
13. 10: Exit For
14. 11: End If
15. 14: Next
16. 15: appExcel.quit ‘Step 5
17. 16: Set appExcel=Nothing ‘Step 6
18. 17: Set objSheet=Nothing
19. 18: Set objWorkBook=Nothing
20. 19: End Function

InputFilePath & SheetName input arguments are passed to the function call paramValue. The output of the previous function will be an entry () matrix containing all the values in column 1 of the given Excel worksheet.

You can call the function above by: paramValues,