The Word MVP Site

• Word Home
• Word:mac
• Word General
• Troubleshoot
• Tutorials
• About Us
• Contact
• Sitemap

• Up to Office inter-development
• Control Outlook from Word
• Control PowerPoint from Word
• Control Word from Excel
• Control Excel from Word
• Disable macros
• Early vs. Late Binding
• Load a ListBox from Excel
• Insert database data
• Microsoft Word MVP FAQ
• Speeding up automation
• Send an email from Word
• Toolbars not showing
• Bug: Instance conflict
• Retrieving from Excel Named Range

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
Dim 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
    rs.MoveNext
Wend

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing