|
Option Explicit
Sub test() 'áD±êìaòaò»Ö£¬áãêÛ±íÖD ±àÂë Îa ìõÂë
Dim f$
f = ThisWorkbook.Path & "\êy¾Y±í.xlsx"
If Dir(f) <> "" Then
Dim Cn As Object, Rs As Object, Sq$, s$
Set Cn = CreateObject("ADODB.Connection")
Set Rs = CreateObject("ADODB.Recordset")
ActiveSheet.UsedRange.Offset(1).ClearContents
Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & f
Set Rs = Cn.OpenSchema(20)
Do Until Rs.EOF
If Rs.Fields("TABLE_TYPE") = "TABLE" Then
s = Replace(Rs("TABLE_NAME").Value, "'", "")
If Right(s, 1) = "$" Then
Sq = Sq & " UNION ALL " & "SELECT ±àÂë,Æ·Ãû,êÛ¼Û,½ø¼Û FROM [" & s & "A1:F]"
End If
End If
Rs.MoveNext
Loop
[a2].CopyFromRecordset Cn.Execute(Mid(Sq, 12))
Rs.Close
Cn.Close
Set Cn = Nothing
Set Rs = Nothing
End If
End Sub
|
|