Importer data fra Access til Excel (DAO)

CopyFromRecordset er den enkleste metoden for å hente data fra en tabell i en Access database og fylle disse inn i et Excel regneark:

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

Dersom du vil ha mer kontroll med dataimporten kan du tilpasse denne makroen:

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

Eksempelmakroene forutsetter at ditt VBA-prosjekt har en referanse til DAO objektbiblioteket.
Dette gjøres i VBE ved ved å velge menyvalget Verktøy, Referanser og krysse av for Microsoft DAO x.xx Object Library.
Bruk ADO hvis du kan velge mellom ADO og DAO for import og eksport av data.

Dokumentet er sist oppdatert 2000-02-05 22:16:41 Utskriftsvennlig versjon