在Excel中使用VBA访问到Excel(ADO)导入数据
通过以下过程,您可以将数据从Access表导入到工作表中。
Sub ADOImportFromAccessTable(DBFullName As String, _ TableName As String, TargetRange As Range) ' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb", _ "TableName", Range("C1") Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer Set TargetRange = TargetRange.Cells(1, 1) ' open the database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ DBFullName & ";" Set rs = New ADODB.Recordset With rs ' open the recordset .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable ' all records '.Open "SELECT * FROM " & TableName & _ " WHERE [FieldName] = 'MyCriteria'", cn, , , adCmdText ' filter records RS2WS rs, TargetRange ' write data from the recordset to the worksheet ' ' optional approach for Excel 2000 or later (RS2WS is not necessary) ' For intColIndex = 0 To rs.Fields.Count - 1 ' the field names ' TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name ' Next ' TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data End With rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub
宏示例假定您的VBA项目已添加对ADO对象库的引用。
您可以通过在VBE中选择“工具”,“引用”并选择“ Microsoft ActiveX数据对象x.x对象库”来执行此操作。
如果可以在ADO和DAO之间进行数据导入或导出选择,请使用ADO。