Testcomplete : To get cell value from excel (.xls) file

'To get the cell value from .xls file workbook you can use the following function directly by passing the following parameters:

'file is the path of the excel file (string)

'sheet is the name of sheet (workbook) (string)

'row and colum are the index of cell's row and column for which you need to get the value (int)

---------

Function GetExcelData(file,sheet,row,colum)

Dim xlApp, xlFile, xlSheet,val

dim iRowCount,iColCount,cLoop

Set xlApp = CreateObject ("Excel.Application") 'Create access object

Set xlFile = xlApp.Workbooks.Open(file)

'Open the excel file,like"C:\myTestData.xls"

Set xlSheet = xlFile.Sheets(sheet) 'Set the sheet name in which the testdata locates

iRowCount = row

iColCount = colum

For rLoop = 1 To iRowCount 'Fetch value from the first row to the last row

'For cLoop = 1 To iColCount ' If you want to get the value cell-to-cell, you can add this line code

'numAdd = numAdd&" "&xlSheet.Cells(rLoop,cLoop)

GetExcelData = xlSheet.Cells(rLoop,colum)

'Next

Next

xlFile.Close

xlApp.Quit

Set xlSheet = Nothing

Set xlFile = Nothing

Set xlApp = Nothing

end function

---------

You can also use this function to get all the values in a row for all the columns in excel file or vice versa by passing rows or column accordingly.

eg: to get all the values in all the rows for column(index=2), call the function in this way:

'row_count is the row count of the .xls file.

sub main

for row = 1 to row_count

msgbox(cstr(GetExcelData(path,sheet, row,2)))

end sub

OR

'To get all the values in all the columns in row (index = 2) , all the function in this way:

'col_count is the row count of the .xls file.

sub main

for col = 1 to col_count

msgbox(cstr(GetExcelData(path,sheet, 2,col)))

end sub