|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Option Explicit
Sub test()
Dim Cn As Object, Sq$, sh As Worksheet, tb$, ta$, c%
Set Cn = CreateObject("ADODB.Connection")
Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=no';Data Source=" & ThisWorkbook.FullName
Sheets("总表").Activate
[d1].Resize(Rows.Count, 40).ClearContents
ta = "[总表$b3:c" & Cells(Rows.Count, 2).End(xlUp).Row & "]a"
c = 4
For Each sh In Worksheets
With sh
If .Name <> ActiveSheet.Name Then
tb = "SELECT * FROM [" & .Name & "$A2:D" & .Cells(.Rows.Count, 1).End(xlUp).Row & "]"
Sq = "SELECT b.f3,b.f4 FROM " & ta & " LEFT JOIN (" & tb & ")b ON a.f1=b.f1"
Cells(1, c) = .Name
Cells(2, c).Resize(1, 2) = .[c1:d1].Value
Cells(3, c).CopyFromRecordset Cn.Execute(Sq)
c = c + 2
End If
End With
Next
Cn.Close
Set Cn = Nothing
End Sub |
评分
-
4
查看全部评分
-
|