VB and VBA Users Source Code: Importing Data From an Excel Workbook into a VB array
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Importing Data From an Excel Workbook into a VB array
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Thursday, February 14, 2008
Hits:
6307
Category:
Visual Basic General
Article:
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
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
