用VBA实现excel与access数据库交互

1、将access中数据导入excel

Public Sub ImportData()Dim mydata As String, mytable As String, SQL As StringDim cnn As ADODB.ConnectionDim rs As ADODB.RecordsetActiveSheet.Cells.Clearmydata = ThisWorkbook.Path & "\成绩管理.mdb" '指定数据库mytable = "考试成绩" '指定数据表'建立与数据库的连接Set cnn = New ADODB.ConnectionWith cnn.Provider = "microsoft.jet.oledb.4.0".Open mydataEnd WithSQL = "select 班级,avg(数学) as 数学平均,avg(语文) as 语文平均," _& "avg(物理) as 物理平均,avg(化学) as 化学平均,avg(英语) as 英语平均, " _& "avg(体育) as 体育平均,avg(总分) as 总分平均 " _& "from " & mytable & " group by 班级"Set rs = New ADODB.Recordsetrs.Open SQL, cnn, adOpenKeyset, adLockOptimistic'复制字段名For i = 1 To rs.Fields.CountCells(1, i) = rs.Fields(i - 1).NameNext i'复制全部数据Range("A2").CopyFromRecordset rsrs.Closecnn.CloseSet rs = NothingSet cnn = NothingEnd Sub

2、将excel数据导入access

Sub 把Excel数据插入数据库中()'*******************************************'时间：2010-06-28'作者：bengdeng'功能：把当前工作表的数据增加到在程序文件同一目录下进销存表数据库中'注意：要在工具/引用中引用microsoft activex date objects x.x' 其中x.x为版本号，可能会因为你安装的office的版本不同而不同，本例引用了2.5版'发布：http://www.excelba.com'*******************************************Dim conn As ADODB.ConnectionDim WN As StringDim TableName As StringDim sSql As StringDim tStr As String'数据库名，请自行修改，路径与当前工作簿在同一目录WN = "进销存表.mdb"'数据库的表名与当前工作表名一致TableName = ActiveSheet.NameSet conn = New ADODB.Connectionconn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;" & _"Extended Properties=Excel 8.0;" & _"Data Source=" & ThisWorkbook.Path & "\" & ActiveWorkbook.Nameconn.OpenIf conn.State = adStateOpen ThensSql = "Insert Into [;DataBase=" & ActiveWorkbook.Path & "\" & WN & "]." & TableName & " Select * From [" & ActiveSheet.Name & "$]"conn.Execute sSqlMsgBox "成功把数据插入到“" & TableName & "”中！", , "http://excelba.com"conn.CloseEnd IfSet conn = NothingEnd Sub