QTP Script: Read all the data from an Excel file

 

=============================================================
' function: ReadXLS
' desc :    Reads a sheet from an XLS file and stores the content
'           in a multi-dimensional array
' params :  strFileName is XLS file to read, including path
'           strSheetName is the name of the sheet to read, i.e "Sheet1"
' returns : Multi-dimensional array containing all data from 
'           the XLS
' =============================================================
Function ReadXLS(strFileName,strSheetName)

Dim strData()
Dim objFS, objExcel, objSheet, objRange
Dim intTotalRow, intTotalCol
Dim intRow, intCol

' create the file system object
Set objFS = CreateObject("Scripting.FileSystemObject")

' ensure that the xls file exists
If Not objFS.FileExists(strFileName) Then

    ' issue a fail if the file wasn't found
    Reporter.ReportEvent micFail, "Read XLS", "Unable to read XLS file, file not found: " & strFileName
    ' file wasn't found, so exit the function
    Exit Function

End If ' file exists

' create the excel object 
Set objExcel = CreateObject("Excel.Application")

' open the file
objExcel.Workbooks.open strFileName

' select the worksheet
Set objSheet = objExcel.ActiveWorkbook.Worksheets(strSheetName)

' select the used range
Set objRange = objSheet.UsedRange

' count the number of rows
intTotalRow=CInt(Split(objRange.Address, "$")(4)) - 1

' count the number of columns
intTotalCol= objSheet.Range("A1").CurrentRegion.Columns.Count

' redimension the multi-dimensional array to accomodate each row and column
ReDim strData(intTotalRow, intTotalCol)

' for each row
For intRow = 0 to intTotalRow - 1

    ' for each column
    For intCol =0 to intTotalCol - 1

        ' store the data from the cell in the array
        strData(intRow, intcol) = Trim(objSheet.Cells(intRow + 2,intcol + 1).Value)

    Next ' column

Next ' row

' close the excel object
objExcel.DisplayAlerts = False
objExcel.Quit 

' destroy the other objects 
Set objFS = Nothing 
Set objExcel = Nothing
Set objSheet = Nothing 

' return the array containing the data
ReadXLS = strData

End Function ' ReadXLS