笨方法:'把Access表的数据导入Excel里(需要引用ADO):Private Sub Command1_Click() Dim cnAccess As New ADODB.Connection, cnExcel As New ADODB.Connection, rsAccess As New ADODB.Recordset, rsExcel As New ADODB.Recordset, i% '打开Access数据库的连接,具体的需要改一下 cnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test.mdb;Persist Security Info=False" rsAccess.CursorLocation = adUseClient '获取Access里的Table1的所有记录,准备导出入Excel rsAccess.Open "select * from table1", cnAccess, adOpenDynamic, adLockReadOnly '连接Excel cnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.xls;Extended Properties=Excel 8.0" rsExcel.CursorLocation = adUseClient '打开Excel的Sheet1表,准备导入数据 rsExcel.Open "select * from [Sheet1$]", cnExcel, adOpenDynamic, adLockPessimistic rsAccess.MoveFirst While Not rsAccess.EOF rsExcel.AddNew For i = 0 To rsAccess.Fields.Count - 1 rsExcel(i) = rsAccess(i) '给Excel的记录集赋值 Next rsAccess.MoveNext Wend rsExcel.UpdateBatch '批量更新记录集 Set rsAccess = Nothing Set rsExcel = Nothing cnAccess.Close Set cnAccess = Nothing cnExcel.Close Set cnExcel = NothingEnd Sub
cnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.xls;Extended Properties=Excel 8.0"cnExcel.Execute "INSERT INTO [sheet1] SELECT * FROM table1 IN """ & App.Path & "\Test.mdb"""
Dim cnAccess As New ADODB.Connection, cnExcel As New ADODB.Connection, rsAccess As New ADODB.Recordset, rsExcel As New ADODB.Recordset, i% '打开Access数据库的连接,具体的需要改一下
cnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test.mdb;Persist Security Info=False"
rsAccess.CursorLocation = adUseClient '获取Access里的Table1的所有记录,准备导出入Excel
rsAccess.Open "select * from table1", cnAccess, adOpenDynamic, adLockReadOnly '连接Excel
cnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.xls;Extended Properties=Excel 8.0"
rsExcel.CursorLocation = adUseClient
'打开Excel的Sheet1表,准备导入数据
rsExcel.Open "select * from [Sheet1$]", cnExcel, adOpenDynamic, adLockPessimistic rsAccess.MoveFirst
While Not rsAccess.EOF
rsExcel.AddNew
For i = 0 To rsAccess.Fields.Count - 1
rsExcel(i) = rsAccess(i) '给Excel的记录集赋值
Next
rsAccess.MoveNext
Wend
rsExcel.UpdateBatch '批量更新记录集 Set rsAccess = Nothing
Set rsExcel = Nothing
cnAccess.Close
Set cnAccess = Nothing
cnExcel.Close
Set cnExcel = NothingEnd Sub