Load a ListBox from a Named Range in Excel using DAO

Article contributed by Ibby and updated for Office 2007 by Doug Robbins

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.

If you are needing to retrieve the data from an Excel 2007 spreadsheet, instead of the reference being to the “Microsoft DAO 3.51 (or 3.6) Object Library, it needs to be to the Microsoft Office 12.0 Access database engine Object Library and the code that follows in this article needs to be modified as indicated.

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
    ' For Office 2003 and earlier spreadsheets use
    Set db = OpenDatabase("C:\Test\Book1.xls", False, False, "Excel 8.0")
    ' For Office 2007 or earlier spreadsheets use
    Set db = OpenDatabase("C:\Test\Book1.xls", False, False, "Excel 12.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


Note, if you are creating such an application for use with Excel 2007 spreadsheets, and you use the modified code as indicated above, it can be used to obtain data from spreadsheets created in earlier versions of Excel if the Microsoft Office 12.0 Access database engine Object Library is available on the system on which your application is running.  That would be the case if Access 2007 is installed on the system.  If it is not, the object library can be downloaded from:

http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

Retrieving Mixed Data Types

When retrieving data from an Excel Spreadsheet using the above method, not all of the data will be retrieved when there is a mixture of data types in the same Excel column.  Actually, the retrieval of data will terminate when there is a change of data type.

This problem is caused by a limitation of the Excel ISAM driver in that once it determines the datatype of an Excel column, it will return a Null for any value that is not of the datatype the ISAM driver has defaulted to for that Excel column. The Excel ISAM driver determines the datatype of an Excel column by examining the actual values in the first few rows (8 by default) and then chooses a datatype that represents the majority of the values in its sampling.

You can work around this by using the following in place of the corresponding command in the above code

    Set db = OpenDatabase("C:\Test\Book1.xls", False, False, "Excel 8.0; IMEX=1;")

or, in the case of data from an Excel 2007 spreadsheet

    Set db = OpenDatabase("C:\Test\Book1.xls", False, False, "Excel 12.0; IMEX=1;")

There is a further limitation of this however and a work around for it in the Knowledge Base article “Excel Values Returned as NULL Using DAO OpenRecordset” at:

http://support.microsoft.com/default.aspx/kb/194124/EN-US#appliesto