Import data from Access to Excel (DAO)

CopyFromRecordset is probably the easiest method of getting data from an Access table to an Excel worksheet.

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.

Document last updated 2000-02-05 12:46:39

User comments:

Ole P. from Norway wrote (2006-06-25 19:22:58 CET):

Re: Error
You are probably missing a reference, take a look in the menu Tools, References...

Peter S from Bayonne, NJ USA wrote (2006-06-25 16:04:09 CET):

Error
Getting the following error

Method 'Range' of object '_Global' failed

Any thoughts / suggestions?

Jeinhor from Sweden wrote (2006-04-27 16:15:15 CET):

Re: How to import with only 4 decimal places?
Wayne,

I had the same problem as you, and it took me about one afternoon to solve (!). I traced the problem back to the Accessdb, more precisely the "Single" data type. If you convert that datatype to "decimal" and set the scale to the maximum number of decimal places currently in your database for that column, excel will read the data correctly. (Try to set the datatype to "double", and you will have the problem you hade in excel in access to... yppiee!)

wayne from singapore wrote (2006-02-28 13:50:40 CET):

How to import with only 4 decimal places?
My data in access, have only 4 decimal places.
It is of data type "single"
When i use the method
"TargetRange.Offset(1, 0).CopyFromRecordset rs"
as shown in the example, the number has a suddenly change from "3.7120" to "3.71199989318847". I don't mind if it is "3.7120000000". Can anyone tell me whether i should do something with Access or change the way i format the number during import?

Thank you

Ole P. from Norway wrote (2006-02-07 21:36:23 CET):

Re: How to access database in a macro which is password protected?
Take a look at the connection string examples here.

Ron from India wrote (2006-02-07 14:26:24 CET):

How to access database in a macro which is password protected?
How to access database in a macro which is password protected?

Ole P. from Norway wrote (2005-06-29 11:38:12 CET):

Re: Delayed Gratification
Like this:

Sub YourOwnProcedure()
DAOCopyFromRecordSet "C:\YourFolderName\YourDataBaseName.mdb", _
"YourTableName", "YourFieldName", Range("C1")
End Sub

VY from Toronto wrote (2005-06-29 05:36:16 CET):

Delayed Gratification
This code wont execute. How do I enter parmeters of the example into the actual code?

Blanco, Odacir (http://geocities.yahoo.com.br/odblanco/) from Manoel Viana - RS - Brasil wrote (2004-10-31 23:21:50 CET):

Interessante
Ole !
Muitp interessane seu artigo, acompanho sua WEB Page a muitos anos e a cada visita aprendo mais com Você.

Sucesso.

Ole P. from Norway wrote (2004-10-09 12:08:18 CET):

Re: Additional Usage Note
If the table name or field name contains spaces you can use square brackets ([ ]) around the name to make the query work. E.g. like this:
Select * from [My Table] where [Field Name] = 0