Question

如何将access中将数据导入excel,用VBA编程实现

Answer 1

Public Sub ImportData()
Dim mydata As String, mytable As String, SQL As String
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
ActiveSheet.Cells.Clear
mydata = ThisWorkbook.Path & "\成绩管理.mdb" '指定数据库
mytable = "考试成绩" '指定数据表
'建立与数据库的连接
Set cnn = New ADODB.Connection
With cnn
.Provider = "microsoft.jet.oledb.4.0"
.Open mydata
End With
SQL = "select 班级,avg(数学) as 数学平均,avg(语文) as 语文平均," _
& "avg(物理) as 物理平均,avg(化学) as 化学平均,avg(英语) as 英语平均, " _
& "avg(体育) as 体育平均,avg(总分) as 总分平均 " _
& "from " & mytable & " group by 班级"
Set rs = New ADODB.Recordset
rs.Open SQL, cnn, adOpenKeyset, adLockOptimistic
'复制字段名
For i = 1 To rs.Fields.Count
Cells(1, i) = rs.Fields(i - 1).Name
Next i
'复制全部数据
Range("A2").CopyFromRecordset rs
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub

Answer 2

录制下 操作过程
数据 导入数据 新建连接数据源
找到
其他里面 有 odbc access的，具体按部就班，选择好后确定
停止录入
查看录入好的过程，
只需要更改其中 access文件路径部分 就是你需要的vba过程

Answer 3

找到表>>右键>>导出>>保存类型>>Microsoft excel>>导出

Answer 4

在Access菜单栏中有转换设置，帮助中有详细解决方法。

Answer 5

用专门的软件