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.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.