» Import data from Access to Excel (DAO) using VBA in Microsoft Excel
VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions
CATEGORY - Import and Export in VBA
VERSION - All Microsoft Excel Versions
Sub DAOCopyFromRecordSet(DBFullName As String, TableName As String, _
FieldName As String, TargetRange As Range)
' Example: DAOCopyFromRecordSet "C:\FolderName\DataBaseName.mdb", _
"TableName", "FieldName", Range("C1")
Dim db As Database, rs As Recordset
Dim intColIndex As Integer
Set TargetRange = TargetRange.Cells(1, 1)
Set db = OpenDatabase(DBFullName)
Set rs = db.OpenRecordset(TableName, dbOpenTable) ' all records
'Set rs = db.OpenRecordset("SELECT * FROM " & TableName & _
" WHERE " & FieldName & _
" = 'MyCriteria'", dbReadOnly) ' filter records
' write field names
For intColIndex = 0 To rs.Fields.Count - 1
TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next
' write recordset
TargetRange.Offset(1, 0).CopyFromRecordset rs
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
If you want more control with the data import, you can customize the macro below:
Sub DAOFromAccessToExcel(DBFullName As String, TableName As String, _
FieldName As String, TargetRange As Range)
' Example: DAOFromAccessToExcel "C:\FolderName\DataBaseName.mdb", _
"TableName", "FieldName", Range("B1")
Dim db As Database, rs As Recordset
Dim lngRowIndex As Long
Set TargetRange = TargetRange.Cells(1, 1)
Set db = OpenDatabase(DBFullName)
Set rs = db.OpenRecordset(TableName, dbOpenTable) ' all records
'Set rs = DB.OpenRecordset("SELECT * FROM " & _
TableName & " WHERE " & FieldName & _
" = 'MyCriteria'", dbReadOnly) ' filter records
lngRowIndex = 0
With rs
If Not .BOF Then .MoveFirst
While Not .EOF
TargetRange.Offset(lngRowIndex, 0).Formula = .Fields(FieldName)
.MoveNext
lngRowIndex = lngRowIndex + 1
Wend
End With
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
The macro examples assumes that your VBA project has added a reference to the DAO object library.You can do this from within the VBE by selecting the menu Tools, References and selecting Microsoft DAO x.xx Object Library.
Use ADO if you can choose between ADO and DAO for data import or export.
Book Store:
Recommended Books:
- The McGraw-Hill 36-Hour Course in Finance for Nonfinancial Managers
- Special Edition Using Microsoft Excel 2002
- Real Estate Finance and Investments (Real Estate Finance and Investments, 11th Ed)
- Finance and Accounting for Nonfinancial Managers
- The Total Money Makeover. : A Proven Plan for Financial Fitness
- Investments + S&P Card + Powerweb + StockTrak discount coupon
No comments have been submitted.

