Load a ListBox from a Named Range in Excel using DAO
Article contributed by Ibby
You can retrieve data from a Named Range in an Excel spreadsheet, without opening the spreadsheet, by using DAO. The advantages of this, compared to automating Excel, is that it is much faster, and it does not require Excel to be installed on the user's machine.
First, you need to set a reference in your project to the “Microsoft DAO 3.51 (or 3.6) Object Library”. To define a range in Excel, select the cells to be included in the range, then go Insert | Name | Define and type a name for the range. Note that the first row in the range is treated as a header row and is not retrieved.
The following example retrieves all the data from a range named “myDatabase” in an Excel file called “Book1.xls” located in folder “C:\Test\”. It then loads this data into a ListBox (ListBox1). The code is simplified by using the GetRows method. The thing to watch out for with the GetRows method is that the retrieved data array is transposed ie: the data in each record occupies one column in the array. This is evident if you use:
ListBox1.List = rs.GetRows(NoOfRecords)
This is where the Column property comes in handy. If you assign the array to the Column property of the ListBox (as in the code below), the array is automatically transposed and the data appears in the correct layout in the ListBox.
Private Sub UserForm_Initialize()
Dim db As
DAO.Database
Dim rs As
DAO.Recordset
Dim NoOfRecords
As Long
' Open the database
Set db =
OpenDatabase("C:\Test\Book1.xls", False,
False, "Excel 8.0")
' Retrieve the recordset
Set rs =
db.OpenRecordset("SELECT * FROM `myDatabase`")
' Determine the number of retrieved
records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
' Set the number of Columns = number
of Fields in recordset
ListBox1.ColumnCount = rs.Fields.Count
' Load the ListBox with the retrieved
records
ListBox1.Column = rs.GetRows(NoOfRecords)
' Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub