Microsoft ExcelでVBAを使用してExcel（DAO）へのアクセスからデータをインポート

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

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