Create an Excel object using VBScript
The function below will allow to you to extract data from an external Excel file (specifying the sheet) without importing the file to the QuickTest data table. The function uses automation objects to create an Excel object.
GetValueInFile (sfilePath, isheet, irow, icolumn)
sfilePath | The path and name of the excel file. |
isheet | The sheet of the excel file. This can be an index number or the sheet name. |
irow | The row number in the table. |
icolumn | The column number in the table. |
Function GetValueInFile (sfilePath, isheet, irow, icolumn)
Set ExcelObj = CreateObject("Excel.Application")
ExcelObj.Workbooks.Open sfilePath
Set NewSheet = ExcelObj.Sheets.Item(isheet)
value = NewSheet.Cells(irow,icolumn)
ExcelObj.Application.Quit
Set ExcelObj = Nothing
GetValueInFile = value
End Function
Example:
dim filename
filename = "D:\temp\Test.xls"
msgbox GetValueInFile(filename,1,2,2)
msgbox GetValueInFile(filename,"Sheet1",1,1)
msgbox GetValueInFile(filename,2,2,2)
msgbox GetValueInFile(filename,3,1,1)