如何使用VBA复制SQL server查询的结果？

楼主 wise	Q：如何使用VBA复制SQL server查询的结果？在使用VBA关联SQL server的时候，需要把执行后的SQL语句返回值返回到Excel工作表中，应该如何编写代码呢？注意：连接SQL server关键需要懂得其服务器、数据库、查询的表。本例通过两种方式返回查询的结果值. A：ALT+F11→插入模块，模块中输入以下代码： Sub 方法1() '****************定义名称********************* Dim conn As Object, rs As Object Dim MyServer As String Dim mydata As String Dim mytable As String Dim SQL As String Dim StartTime As Variant Dim i As Long, j As Long Dim ws As Worksheet StartTime = Timer Set ws = Sheet1 ws.Cells.ClearContents '*******************需要修改此处的SQL Server 数据库条件************************** MyServer = "WISE-THINK" '存放数据的SQL Server 服务器 mydata = "271模型" '存放数据的SQL Server 数据库 mytable = "[201207]" '存放数据的数据表 '*******************修改结束******************************************************* Set conn = CreateObject("ADODB.Connection") '建立ADODB连接对象 Set rs = CreateObject("ADODB.recordset") '建立收集器对象 conn.connectionstring = "Driver={sql server};" _ & "server=" & MyServer & ";" _ & "uid=;pwd=;" _ & "database=" & mydata _ & ";AutoTranslate=False" '服务器是:WISE-THINK conn.Open '打开连接SQL server '提取前10个记录 SQL = "SELECT Top 10 * from " & mytable '执行SQL语句 Set rs = conn.Execute(SQL) '复制表头名并字体加粗 For i = 0 To rs.Fields.Count - 1 ws.Cells(1, i + 1) = rs.Fields(i).Name ws.Cells(1, i + 1).Font.Bold = True Next i '复制全部记录数据 ws.Range("A2").CopyFromRecordset rs ws.Columns.AutoFit '关闭记录集合及数据库连接 rs.Close conn.Close Set rs = Nothing Set conn = Nothing MsgBox Timer - startime End Sub 方法二： Sub 方法2() '****************定义名称********************* Dim conn As Object, rs As Object Dim MyServer As String Dim mydata As String Dim mytable As String Dim SQL As String Dim StartTime As Variant Dim i As Long, j As Long Dim ws As Worksheet StartTime = Timer Set ws = Sheet1 ws.Cells.ClearContents '*******************需要修改此处的SQL Server 数据库条件************************** MyServer = "WISE-THINK" '存放数据的SQL Server 服务器 mydata = "271模型" '存放数据的SQL Server 数据库 mytable = "[201207]" '存放数据的数据表 '*******************修改结束******************************************************* Set conn = CreateObject("ADODB.Connection") '建立ADODB连接对象 Set rs = CreateObject("ADODB.recordset") '建立收集器对象 conn.connectionstring = "Driver={sql server};" _ & "server=" & MyServer & ";" _ & "uid=;pwd=;" _ & "database=" & mydata _ & ";AutoTranslate=False" '服务器是:WISE-THINK conn.Open '打开连接SQL server '提取前10个记录 SQL = "SELECT Top 10 * from " & mytable '执行SQL语句 Set rs = conn.Execute(SQL) '复制表头名并字体加粗 For i = 0 To rs.Fields.Count - 1 ws.Cells(1, i + 1) = rs.Fields(i).Name ws.Cells(1, i + 1).Font.Bold = True Next i '复制全部数据 i = 1 Do While Not rs.EOF For j = 0 To rs.Fields.Count - 1 ws.Cells(i + 1, j + 1) = rs.Fields(j).Value Next j rs.movenext i = i + 1 Loop '设置列宽到合适的宽度 ws.Columns.AutoFit '关闭记录集合及数据库连接 rs.Close conn.Close Set rs = Nothing Set conn = Nothing MsgBox Timer - startime End Sub SQL查询结果复制两种方法.rar
2楼纵鹤擒龙水中月	学习了
3楼 335081548	谢谢分享
4楼芐雨	学习了
5楼老糊涂	学习了