06-20-2012, 03:07 PM
Here is an example to cast an excel range to a 2D- array
Code:
Call ReadExcel (sFileName,sSheetName)
Function ReadExcel(sFileName,sSheetName)
Dim oExcel
Dim oRange
Dim arrRange
'Open the file and set the sheet
'On Error Resume Next
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible=True
oExcel.Workbooks.Open "C:\Documents and Settings\testingsvs_qa\Desktop\Book1.xls"
oExcel.Workbooks(1).Activate
Set oRange = oExcel.Worksheets(sSheetName).UsedRange
If Err.Number <> 0 Then
ReadExcel = Array("Error")
msgbox Err.Description
Exit Function
End If
'On Error Goto 0
'Cast excel data into a two-dimentional array
arrRange = oRange.Value
oExcel.WorkBooks.Close
Set oRange = Nothing
oExcel.Quit
Set oExcel = Nothing
'return the arrRange and then return to the function
ReadExcel = arrRange
End Function