Importar datos de acceso a Excel (DAO) utilizando VBA en Microsoft Excel
CopyFromRecordset es probablemente el método más sencillo para obtener datos de una tabla de Access en una hoja de cálculo de 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
Si desea más control con la importación de datos, puede personalizar la macro a continuación:
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
Los ejemplos de macros asumen que su proyecto VBA ha agregado una referencia a la biblioteca de objetos DAO.
Puede hacer esto desde el VBE seleccionando el menú Herramientas, Referencias y seleccionando Microsoft DAO x.xx Object Library.
Utilice ADO si puede elegir entre ADO y DAO para la importación o exportación de datos.