Retrieving Data 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 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.

The following example retrieves all the data from the first Field (column) in a range named myDatabase in an Excel file called Book1.xls located in folder C:\Test\.

Dim db As DAO.Database
rs As DAO.Recordset

Set db = OpenDatabase("C:\Test\Book1.xls", False, False, "Excel 8.0")
Set rs = db.OpenRecordset("SELECT * FROM `myDatabase`")

While Not rs.EOF
    MsgBox rs.Fields(0).Value


Set rs = Nothing
Set db = Nothing