请教下我想用SQL查询除了查询表之外的所有的工作表，这个VBA代码要怎么修改？

cai2532

请教下我想用VBA SQL查询除了查询表之外的所有的工作表，这个VBA代码要怎么修改？
Sub SQL多表查询()
On Error Resume Next
Application.ScreenUpdating = False
With Sheets("数据查询")
Dim Cn As Object, Sq$(2)
.Range("c4:l" & .Cells(Rows.Count, 2).End(xlDown).Row).ClearContents
  Set Cn = CreateObject("ADODB.Connection")
  Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=no';Data Source=" & ThisWorkbook.FullName
  Sq(0) = "[" & "数据查询" & "$a4:b" & Cells(Rows.Count, 2).End(xlUp).Row & "]a"
  Sq(1) = "[Excel 12.0;HDR=no;Database=" & ThisWorkbook.FullName & "].[初一各科成绩$A3:l]b"
  Sq(2) = "SELECT b.f3,b.f4,b.f5,b.f6,b.f7,b.f8,b.f9,b.f10,b.f11 FROM " & Sq(0) & " LEFT JOIN " & Sq(1) & " ON a.f2=b.f2 "
  Range("c4").CopyFromRecordset Cn.Execute(Sq(2))
  Cn.Close: Set Cn = Nothing
  For k = 4 To .Cells(Rows.Count, 2).End(xlDown).Row
    n = n + 1
  If .Cells(k, 2) <> "" Then .Cells(k, 1) = n
Next
Sheets("数据查询").Activate
End With
Application.ScreenUpdating = True
End Sub

3190496160

还得匹配数据，就不是sql的优势了，用数组+字典，速度会比sql高效

mmwwdd

循环，把文件名取出来

cai2532

3190496160 发表于 2022-12-27 15:57
还得匹配数据，就不是sql的优势了，用数组+字典，速度会比sql高效

可以帮忙写个数组或字典代码吗?

cai2532

mmwwdd 发表于 2022-12-27 16:01
循环，把文件名取出来

可以帮忙看看吗？

mmwwdd

mmwwdd

这两天SQLl挺多，我正好在弄SQL

cai2532

mmwwdd 发表于 2022-12-27 16:27
这两天SQLl挺多，我正好在弄SQL

真的太感谢了！

mmwwdd

cai2532 发表于 2022-12-27 17:03
真的太感谢了！

给花或者给钱都能表示你的感谢

fzxba

1. Sub test1() '
   
2.   Dim Conn As Object, Sht As Worksheet
   
3.   Dim ar, i As Integer, strFields As String, SQL As String
   
4.   
   
5.   Set Conn = CreateObject("ADODB.Connection")
   
6.   If Application.Version < 12 Then
   
7.     Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
   
8.   Else
   
9.     Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
   
10.   End If
    
11.   
    
12.   Worksheets("数据查询").Activate
    
13.   
    
14.   ar = Range("A1").CurrentRegion.Rows(3)
    
15.   strFields = "a.*"
    
16.   For i = 3 To UBound(ar, 2)
    
17.     strFields = strFields & ",b." & ar(1, i)
    
18.   Next
    
19.   
    
20.   For Each Sht In Worksheets
    
21.     With Sht
    
22.       If .Name <> ActiveSheet.Name Then
    
23.         SQL = SQL & " UNION ALL SELECT * FROM [" & .Name & "$B3:L" & .Cells(.Rows.Count, "B").End(xlUp).Row & "]"
    
24.       End If
    
25.     End With
    
26.   Next
    
27.   
    
28.   SQL = "SELECT " & strFields & " FROM [" & ActiveSheet.Name & "$A3:B" & Cells(Rows.Count, "B").End(xlUp).Row & "] a " & _
    
29.         "LEFT JOIN (" & Mid(SQL, 12) & ") b ON a.姓名=b.姓名"
    
30.   
    
31.   Range("A4").CopyFromRecordset Conn.Execute(SQL)
    
32.   
    
33.   Conn.Close
    
34.   Set Conn = Nothing
    
35.   Beep
    
36. End Sub

复制代码