Sql in VBA 之 记录集赋值给数组

关键:arr=rst.Getrows

数据表如下:

姓名	成绩
甲	134
乙	84
丙	56
丁	142
戊	94
己	65
庚	79
辛	126
壬	53
癸	87
子	135
丑	85
寅	68
卯	90
辰	119
巳	41
午	118
未	141
申	82
酉	101
戌	107
亥	57

代码如下:

 1 查询第6-15名的学生信息
 2 Sub GetRSwithArray()
 3     Dim cnn As Object
 4     Dim rst As Object
 5     Dim strSQL$, strPath$
 6     Dim aData, aResult
 7     Dim i&, j&
 8      9     strPath = ThisWorkbook.FullName
10     Set cnn = CreateObject("ADODB.Connection")
11     Set rst = CreateObject("ADODB.Recordset")
12     If Application.Version < 12 Then
13         cnn.Open "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & strPath
14     Else
15         cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & strPath
16     End If
17     strSQL = "SELECT top 15 姓名,成绩 FROM [成绩表$] order by 成绩 desc"
18     前15位
19     rst.Open strSQL, cnn, 1, 3
20     aData = rst.Getrows
21     记录集转二维数组
22     ReDim aResult(0 To UBound(aData, 2), 0 To UBound(aData, 1) + 1)
23     
24     声明一个新数组,将记录集数组转换过去(转置)
25     For i = 5 To UBound(aData, 2)
26         For j = 0 To UBound(aData, 1)
27             aResult(i - 5, j) = aData(j, i)
28         Next
29         aResult(i - 5, 2) = i + 1
30         名次
31     Next
32     
33     Cells.ClearContents
34     [a1:c1] = Array("姓名", "成绩", "名次")
35     Range("a2").Resize(UBound(aResult), 3) = aResult
36     rst.Close
37     cnn.Close
38     Set rst = Nothing
39     Set cnn = Nothing
40 End Sub

 

Sql in VBA 之 记录集赋值给数组

上一篇:Host is not allowed to connect to this MySQL server 解决方法


下一篇:mongoDB