👁0views

Importing Data From an Excel Workbook into a VB array

Option Explicit

'Purpose     :  Reads an excel workbook's worksheet into a 2d array
'Inputs      :  sWorkbookPath           The path of the workbook to return the data from.
'               [sSheetName]            The name of the worksheet to return the data from.
'Outputs     :  Returns True if successful
'Author      :  Andrew Baker
'Date        :  31/12/2000 13:51
'Notes       :
'Revisions   :

Function ImportWorksheetFromExcel(sWorkbookPath As String, Optional sSheetName As String = "") As Variant
    Dim oExcel As Object
    Dim oWorkbook As Object
    Dim oWkSheet As Object
    Dim avValues As Variant
    
    On Error Resume Next
    'Check if file exists
    If Len(Dir$(sWorkbookPath)) > 0 Then
        Set oExcel = CreateObject("Excel.Application")
        If (oExcel Is Nothing) = False Then
            On Error GoTo ErrFailed
            'Successfully created an Excel Application
            'Open workbook
            Set oWorkbook = oExcel.Workbooks.Open(sWorkbookPath, False, True)
            'Add sheet to store results
            If Len(sSheetName) > 0 Then
                Set oWkSheet = oWorkbook.Sheets(sSheetName)
            Else
                'Just use first sheet
                Set oWkSheet = oWorkbook.Sheets(1)
            End If
            
            'Get used range
            Call RangeToArray(oWkSheet.UsedRange, avValues)
            
            'Close Excel
            oWorkbook.Close False
            oExcel.Quit
            Set oExcel = Nothing
        End If
    End If
    
    ImportWorksheetFromExcel = avValues
    Exit Function

ErrFailed:
    Debug.Assert False
    Debug.Print Err.Description
    On Error GoTo 0
End Function

'Purpose     :  Reads the values of a range into an array (much quicker than looping through a range)
'Inputs      :  rngInput                The range to extract the values from.
'               avValues                See outputs.
'Outputs     :  Returns the True on success.
'               avValues                An 2d array containing the values in the range.
'Author      :  Andrew Baker
'Date        :  31/12/2000 13:51
'Notes       :
'Revisions   :
'Example     :  Call RangeToArray(Worksheets(1).Range("A1:K1000"), avValues)

Function RangeToArray(rngInput As Object, avValues As Variant) As Boolean
    On Error GoTo ErrFailed
    avValues = Empty
    avValues = rngInput.Value
    RangeToArray = True
    
    Exit Function

ErrFailed:
    'Failed
    Debug.Print "Error in RangeToArray: " & Err.Description
    Debug.Assert False
    RangeToArray = False
    On Error GoTo 0
End Function

'Demonstration routine.
Sub Test()
    Dim avData As Variant, vCell As Variant

    avData = ImportWorksheetFromExcel("C:\Book1.xls")
    For Each vCell In avData
        Debug.Print "Cell: " + vCell
    Next
End Sub