14 Feb 2008 Open Source

Import Excel Workbook Data Into a VB Array: How-To

👁29views

Reading Excel workbook data into a VB array requires referencing the Microsoft Excel Object Library, then using `CreateObject("Excel.Application")` to open the workbook. Iterate through each cell in the target worksheet and assign values to a dimensioned array using row and column index counters. Close the workbook and release the Excel object after populating the array.

CloudScale AI SEO - Article Summary

1.
What it is
This article provides VB code for loading Excel workbook data directly into an array variable within a Visual Basic application.
2.
Why it matters
This technique eliminates the need for manual data entry or complex file parsing when your VB application needs to work with spreadsheet data.
3.
Key takeaway
You can programmatically read Excel data into VB arrays, streamlining data import workflows.

~1 min read

The following code can be used to import the contents of an excel workbook into an array from within a Visual Basic application.

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