VBA 如何执行多条SQL语句并返回结果

cnxwj

请问：如何执行多条SQL语句并返回结果，附件中执行3次不同SQL查询，参考了本论坛9楼的方法未实现。谢谢。

https://club.excelhome.net/thread-1413516-1-1.html  //9楼

执行多条SQL语句返回结果.7z (13.19 KB, 下载次数: 8)

2022-11-23 14:53 上传

点击文件名下载附件

WANT-T

这样：

1. "SELECT '表1区领导批示件' AS 类别,分管领导,牵头科室,经办人 FROM [表1$] " & "UNION ALL " & _
   
2. 
   
3. "SELECT '表2区领导批示件' AS 类别,分管领导,牵头科室,经办人 FROM [表2$] " & "UNION ALL " & _
   
4. 
   
5. "SELECT '表3区领导批示件' AS 类别,分管领导,牵头科室,经办人 FROM [表3$] "

复制代码

fzxba

1. Sub test()
   
2.   Dim Conn As Object, rs As Object, Sht As Worksheet
   
3.   Dim strConn As String, ar() As String, i As Integer
   
4.   Set Conn = CreateObject("ADODB.Connection")
   
5.   
   
6.   If Application.Version < 12 Then
   
7.     strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source="
   
8.   Else
   
9.     strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source="
   
10.   End If
    
11.   Conn.Open strConn & ThisWorkbook.FullName
    
12.   
    
13.   For Each Sht In Worksheets
    
14.     With Sht
    
15.       If .Name <> "汇总" Then
    
16.         i = i + 1
    
17.         ReDim Preserve ar(1 To i)
    
18.         ar(i) = "SELECT '" & .Name & "区领导批示件' AS 类别,分管领导,牵头科室,经办人 FROM [" & .Name & "$" & .Range("A1").CurrentRegion.Address(0, 0) & "]"
    
19.       End If
    
20.     End With
    
21.   Next
    
22.   
    
23.   Set rs = Conn.Execute(Join(ar, " UNION ALL "))
    
24.   With Worksheets("汇总")
    
25.     .Range("A1").CurrentRegion.Offset(1).ClearContents
    
26.     For i = 0 To rs.Fields.Count - 1
    
27.       .Range("A1").Offset(0, i) = rs.Fields(i).Name
    
28.     Next
    
29.     .Range("A2").CopyFromRecordset rs
    
30.   End With
    
31.   
    
32.   Set rs = Nothing
    
33.   Conn.Close
    
34.   Set Conn = Nothing
    
35.   Beep
    
36. End Sub

复制代码

cnxwj

WANT-T 发表于 2022-11-23 15:09
这样：

谢谢，测试通过。

cnxwj

fzxba 发表于 2022-11-23 15:32

谢谢，测试通过，学习了。

sdytsxd991122