Import údajov z programu Access do Excelu (DAO) pomocou jazyka VBA v programe Microsoft Excel

Anonim

CopyFromRecordset je pravdepodobne najľahší spôsob získavania údajov z tabuľky programu Access do pracovného hárka programu Excel.

Sub DAOCopyFromRecordSet (DBFullName As String, TableName As String, _ FieldName As String, TargetRange As Range) 'Príklad: 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' názvy polí zápisu pre intColIndex = 0 Do rs.Fields.Count - 1 TargetRange.Offset ( 0, intColIndex) .Hodnota = rs.Fields (intColIndex) .Name Next 'write recordset TargetRange.Offset (1, 0) .CopyFromRecordset rs Set rs = Nothing db.Close Set db = Nothing End Sub

Ak chcete väčšiu kontrolu nad importom údajov, môžete si makro prispôsobiť nižšie:

Sub DAOFromAccessToExcel (DBFullName As String, TableName As String, _ FieldName As String, TargetRange As Range) 'Príklad: 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 = .Pole (FieldName) .MoveNext lngRowIndex = lngRowIndex + 1 Wend End With Set rs = Nothing db.Close Set db = Nothing End Sub

Príklady makier predpokladajú, že váš projekt VBA pridal odkaz na knižnicu objektov DAO.
Môžete to urobiť z VBE tak, že vyberiete ponuku Nástroje, Referencie a vyberiete Objektovú knižnicu Microsoft DAO x.xx.
Použite ADO, ak si môžete vybrať medzi ADO a DAO na import alebo export údajov.