|
Option Explicit
Sub test()
Dim Cn As Object, Sq$, ar, s$, sh As Worksheet
Set Cn = CreateObject("ADODB.Connection")
Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
Worksheets("Sheet7").Activate
ar = ActiveSheet.[a1:m1&""]
s = "[" & Join(ar, "],[") & "]"
For Each sh In Worksheets
If sh.Name <> ActiveSheet.Name Then
Sq = Sq & " UNION ALL SELECT " & s & " FROM [" & sh.Name & "$A1:Y] WHERE [Type] IS NOT NULL"
End If
Next
ActiveSheet.UsedRange.Offset(1).Clear 'Contents
[a2].CopyFromRecordset Cn.Execute(Mid(Sq, 12))
[a1].CurrentRegion.Offset(1, 1).NumberFormatLocal = "0%"
Cn.Close
Set Cn = Nothing
End Sub |
评分
-
2
查看全部评分
-
|