CopyFromRecordset er sandsynligvis den nemmeste metode til at hente data fra en Access -tabel 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 feltnavne For intColIndex = 0 Til rs.Fields.Count - 1 TargetRange.Offset ( 0, intColIndex) .Value = rs.Fields (intColIndex) .Name Næste 'skriv recordset TargetRange.Offset (1, 0) .CopyFromRecordset rs Set rs = Nothing db.Close Set db = Intet ende Sub
Hvis du vil have mere kontrol med dataimporten, kan du tilpasse makroen herunder:
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 Herefter .MoveFirst While Not .EOF TargetRange.Offset ( lngRowIndex, 0) .Formula = .Fields (FieldName) .MoveNext lngRowIndex = lngRowIndex + 1 Afslut med sæt rs = Intet db.Luk sæt db = Intet ende Sub
Makroeksemplerne forudsætter, at dit VBA -projekt har tilføjet en reference til DAO -objektbiblioteket.
Du kan gøre dette inde fra VBE ved at vælge menuen Værktøjer, Referencer og vælge Microsoft DAO x.xx Object Library.
Brug ADO, hvis du kan vælge mellem ADO og DAO til dataimport eller eksport.