CopyFromRecordset er sannsynligvis den enkleste metoden for å få data fra en Access -tabell til et Excel -regneark.
Sub DAOCopyFromRecordSet (DBFullName As String, TableName As String, _ FieldName As String, TargetRange As Range) 'Eksempel: 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' skrive feltnavn For intColIndex = 0 Til rs.Fields.Count - 1 TargetRange.Offset ( 0, intColIndex) .Value = rs.Fields (intColIndex) .Name Neste 'skriv postsett TargetRange.Offset (1, 0) .CopyFromRecordset rs Set rs = Nothing db.Close Set db = Nothing End Sub
Hvis du vil ha mer kontroll med dataimporten, kan du tilpasse makroen nedenfor:
Sub DAOFromAccessToExcel (DBFullName As String, TableName As String, _ FieldName As String, TargetRange As Range) 'Eksempel: 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 Med 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
Makroeksemplene forutsetter at VBA -prosjektet ditt har lagt til en referanse til DAO -objektbiblioteket.
Du kan gjøre dette fra VBE ved å velge menyen Verktøy, referanser og velge Microsoft DAO x.xx objektbibliotek.
Bruk ADO hvis du kan velge mellom ADO og DAO for dataimport eller eksport.