CopyFromRecordset可能是将数据从Access表获取到Excel工作表的最简单方法。

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

如果要对数据导入进行更多控制，则可以自定义以下宏：

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

宏示例假定您的VBA项目已添加对DAO对象库的引用。

您可以通过在VBE中选择“工具”，“引用”并选择“ Microsoft DAO x.xx对象库”来执行此操作。

如果可以在ADO和DAO之间进行数据导入或导出选择，请使用ADO。