Read Excel Range into an Array
Posted by admin - May 1, 2008 Articles, MS-Excel, QTips, Yaron Assa 0 0 Views : 1159 Receive Updates For This Category
Article Tools
- Print this page
- Add Comment
- Send to Friend
- Last Updated on :
Jul 16, 2011
Usually, we only want to read some data from an Excel worksheet, and we’ll be more than happy to get that data in a simple 2-dimensional array.
Later we can loop through the array, and access the data in a quick and easy fashion.
Here’s a function which receives an Excel file, the workbook name and returns its contents as a two dimensional array.
1: Function ReadExcel(sFileName,sSheetName)
2: Dim oExcel
3: Dim oRange
4: Dim arrRange
5:
6: 'Open the file and set the sheet
7: On Error Resume Next
8: Set oExcel = CreateObject("Excel.Application")
9: oExcel.Workbooks.Open(1)
10: Set oRange = oExcel.Worksheets(sSheetName).UsedRange
11:
12: If Err.Number <> 0 Then
13: ReadExcel = Array("Error")
14: Exit Function
15: End If
16: On Error Goto 0
17:
18: 'Cast excel data into a two-dimentional array
19: arrRange = oRange.Value
20:
21: oExcel.WorkBooks.Item(sFileName).Close
22: Set oRange = Nothing
23: oExcel.Quit
24: Set oExcel = Nothing
25:
26: 'return the arrRange and then return to the function
27: ReadExcel = arrRange
28:
29: End Function
