第1个回答 2011-05-13
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本回答被提问者采纳