Sunday, August 12, 2007

How to Read Xls Sheet data from QTP

We can create Xls object (As that is created in VB) and call that objest as a function.By this we can easily read any Xls file placed at any where in our machine (May be on network also).
For this script there is NO need of Object Repository of QTP.
Here is the Function :

Function ReadfromXls(absolutepath,sheetname,row,col)

Dim cellvalue1Dim ExcelApp 'As Excel.Application

Dim NewWorkbook 'As Excel.workbook
Dim worksheet 'As Excel.worksheet

Set ExcelApp = CreateObject("Excel.Application") 'Create a new excel Object
ExcelApp.Visible = False
Set NewWorkbook = ExcelApp.Workbooks.Open(absolutepath)
Set worksheet= NewWorkbook.Sheets(sheetname)
cellvalue1 = worksheet.Cells(row,col)

ReadfromXls = cellvalue1

NewWorkbook.Close
ExcelApp.Quit
Set ExcelApp = Nothing

End Function



Call to Function :ReadfromXls("C:\Test\test1.xls","sheet3",2,4)


Result of this Call :

This call return the data present in Second Row and D Column (D means 4th column of Xls).
The path of Xls file include the NAME of file also.And the sheet name is also specified.

No comments: