VBA链接多个工作簿汇总的三种写法。

yeyooo

1. Sub VBA_SQL()
   
2.     Dim AdoConn As New ADODB.Connection
   
3.     Dim strConn As String
   
4.     Dim strSQL As String
   
5. 
   
6.     strConn = " Provider=Microsoft.ACE.OLEDB.12.0;" & _
   
7.                 "Data Source=" & ThisWorkbook.FullName & _
   
8.                 ";Extended Properties=""Excel 12.0;HDR=YES"";"
   
9. 
   
10.     strSQL = "Select * FROM [EXCEL 12.0;HDR=YES;DATABASE=" & ThisWorkbook.Path & Application.PathSeparator & _
    
11.              "USER 1.xlsx;" & "].[USER$]" & vbCrLf & "UNION ALL" & vbCrLf & _
    
12.              "Select * FROM [EXCEL 12.0;HDR=YES;DATABASE=" & ThisWorkbook.Path & Application.PathSeparator & _
    
13.              "USER 2.xlsx;" & "].[USER$]" & vbCrLf & "UNION ALL" & vbCrLf & _
    
14.              "Select * FROM [EXCEL 12.0;HDR=YES;DATABASE=" & ThisWorkbook.Path & Application.PathSeparator & _
    
15.              "USER 3.xlsx;" & "].[USER$]" & vbCrLf & "UNION ALL" & vbCrLf & _
    
16.              "Select * FROM [EXCEL 12.0;HDR=YES;DATABASE=" & ThisWorkbook.Path & Application.PathSeparator & _
    
17.              "USER 4.xlsx;" & "].[USER$]"
    
18. 
    
19.     AdoConn.Open strConn
    
20. 
    
21.     Sheets(1).Range("A2").CopyFromRecordset AdoConn.Execute(strSQL)
    
22. 
    
23.     AdoConn.Close
    
24. End Sub
    
25. 
    
26. 
    
27. Sub VBA_SQL_1()
    
28.     Dim AdoConn As New ADODB.Connection
    
29.     Dim strConn As String
    
30.     Dim strSQL As String
    
31. 
    
32.     strConn = " Provider=Microsoft.ACE.OLEDB.12.0;" & _
    
33.                 "Data Source=" & ThisWorkbook.FullName & _
    
34.                 ";Extended Properties=""Excel 12.0;HDR=YES"";"
    
35. 
    
36.     strSQL = "Select * FROM [" & ThisWorkbook.Path & Application.PathSeparator & _
    
37.              "USER 1.xlsx;" & "].[USER$]" & vbCrLf & "UNION ALL" & vbCrLf & _
    
38.              "Select * FROM [" & ThisWorkbook.Path & Application.PathSeparator & _
    
39.              "USER 2.xlsx;" & "].[USER$]" & vbCrLf & "UNION ALL" & vbCrLf & _
    
40.              "Select * FROM [" & ThisWorkbook.Path & Application.PathSeparator & _
    
41.              "USER 3.xlsx;" & "].[USER$]" & vbCrLf & "UNION ALL" & vbCrLf & _
    
42.              "Select * FROM [" & ThisWorkbook.Path & Application.PathSeparator & _
    
43.              "USER 4.xlsx;" & "].[USER$]"
    
44. 
    
45.     AdoConn.Open strConn
    
46. 
    
47.     Sheets(1).Range("A2").CopyFromRecordset AdoConn.Execute(strSQL)
    
48. 
    
49.     AdoConn.Close
    
50. End Sub
    
51. 
    
52. Sub VBA_SQL_2()
    
53.     Dim AdoConn As New ADODB.Connection
    
54.     Dim strConn As String
    
55.     Dim strSQL As String
    
56. 
    
57.     strConn = " Provider=Microsoft.ACE.OLEDB.12.0;" & _
    
58.                 "Data Source=" & ThisWorkbook.FullName & _
    
59.                 ";Extended Properties=""Excel 12.0;HDR=YES"";"
    
60. 
    
61.     strSQL = "Select * FROM [D:\USER 1.xlsx;" & "].[USER$]" & vbCrLf & "UNION ALL" & vbCrLf & _
    
62.              "Select * FROM [D:\USER 2.xlsx;" & "].[USER$]" & vbCrLf & "UNION ALL" & vbCrLf & _
    
63.              "Select * FROM [D:\USER 3.xlsx;" & "].[USER$]" & vbCrLf & "UNION ALL" & vbCrLf & _
    
64.              "Select * FROM [D:\USER 4.xlsx;" & "].[USER$]"
    
65. 
    
66.     AdoConn.Open strConn
    
67. 
    
68.     Sheets(1).Range("A2").CopyFromRecordset AdoConn.Execute(strSQL)
    
69. 
    
70.     AdoConn.Close
    
71. End Sub
    

复制代码

VBA链接多个工作簿汇总的三种写法，留着总有用上的时候。

Kelidai

不错的总结。

zez

谢谢分享!!

fxwang1967

感谢分享！

when123456

谢谢分享，超值

芳菲887

向高手学习

minjiwei

谢谢分享！