Sql in VBA 之 初识ADO
少年不知……，废话不多说，直接贴代码，
学生表：
ID 姓名 性别 年龄 爱好 得分 1 张三 男 14 发呆 55 2 李四 女 15 熬夜 64 3 王五 男 10 竞走 55 4 赵六 女 16 化妆 79 5 麻七 男 10 画画 65 6 大红 女 13 养花 3 7 大红花 女 10 送化 1
在Excel VBA中用ADO要注意Excel版本号，用 Application.Version 来判断：
1 If Application.Version < 12 Then 2 Str_cnn = "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & MyPath 3 Else 4 Str_cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & MyPath 5 End If
下面上整体代码（用Execute来执行ADO）：
注意：HDR=yes表示第一行是标题
1 Sub DoSql_Execute() 2 Dim cnn As Object, rest As Object 3 Dim MyPath$, Str_cnn$, Sql$ 4 Dim i& 5 Set cnn = CreateObject("adodb.connection") '后期绑定ADO 6 7 MyPath = ThisWorkbook.FullName '数据源工作簿的完整路径 8 9 If Application.Version < 12 Then '判断Excel版本号，以使用不同的连接字符串 10 Str_cnn = "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=yes';Data Source=" & MyPath 11 Else 12 Str_cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes';Data Source=" & MyPath 13 End If 14 cnn.Open Str_cnn '建立链接 15 16 Sql = "select * from [学生表$] " 'sql语句 17 Set rest = cnn.Execute(Sql) '执行sql,始终得到一个新的记录集 18 19 [a:f].ClearContents '清除[d:e]区域的值 20 21 For i = 0 To rest.Fields.Count - 1 '利用fields属性获取字段名 22 Cells(1, i + 1) = rest.Fields(i).Name 23 Next 24 Range("a2").CopyFromRecordset rest '使用单元格对象的CopyFromRecordset方法将rest的内容复制过来 25 26 cnn.Close 27 28 Set cnn = Nothing 29 30 End Sub
下面用 rst 执行SQL语句：
1 Sub DoSql_Recordset() 2 Dim cnn As Object, rst As Object 3 Dim Mypath As String, Str_cnn As String, Sql As String 4 Dim i As Long 5 Set cnn = CreateObject("adodb.connection") 6 Set rst = CreateObject("adodb.Recordset") 7 Mypath = ThisWorkbook.FullName 8 9 If Application.Version < 12 Then 10 Str_cnn = "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & Mypath 11 Else 12 Str_cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & Mypath 13 End If 14 cnn.Open Str_cnn 15 16 Sql = "SELECT 姓名,成绩 FROM [Sheet1$] WHERE 成绩>=80" 17 18 rst.Open Sql, cnn, 1, 3 19 'rst执行SQL语句，并将查询结果放入记录集 20 21 [d:e].ClearContents 22 '清空[d:e]区域的值 23 For i = 0 To rst.Fields.Count - 1 24 '利用fields属性获取所有字段名，fields包含了当前记录有关的所有字段,fields.count得到字段的数量 25 '由于Fields.Count下标为0，又从0开始遍历，因此总数-1 26 Cells(1, i + 4) = rst.Fields(i).Name 27 Next 28 Range("d2").CopyFromRecordset rst 29 '使用单元格对象的CopyFromRecordset方法将rst内容复制到D2单元格为左上角的单元格区域 30 31 rst.Close 32 cnn.Close 33 34 Set rst = Nothing 35 Set cnn = Nothing 36 '释放内存 37 End Sub
