|
Option Explicit
Sub test()
Dim d As Object, Cn As Object, Rs As Object, Rst As Object, p$, f$, s$, sq$, sh As Worksheet, i%
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each sh In Worksheets
If sh.Index > 1 Then sh.Delete
Next
Application.DisplayAlerts = True
Set d = CreateObject("Scripting.Dictionary")
Set Cn = CreateObject("ADODB.Connection")
p = ThisWorkbook.Path & "\"
f = Dir(p & "*.xls*")
Do While f <> ""
If f <> ThisWorkbook.Name Then
Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & p & 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 = "Select * From [" & s & "]"
Set Rst = Cn.Execute(sq)
If Not d.Exists(s) Then
d(s) = Left(s, Len(s) - 1)
Worksheets.Add after:=Worksheets(Worksheets.Count)
With ActiveSheet
.Name = d(s)
For i = 0 To Rst.Fields.Count - 1
.[a1].Offset(0, i) = Rst.Fields(i).Name
Next
.[a2].CopyFromRecordset Rst
End With
Else
Worksheets(d(s)).Cells(Rows.Count, 1).End(xlUp).Offset(1).CopyFromRecordset Rst
End If
Set Rst = Nothing
End If
End If
Rs.MoveNext
Loop
Rs.Close
Cn.Close
End If
f = Dir
Loop
Set d = Nothing
Set Cn = Nothing
Set Rs = Nothing
Worksheets(1).Activate
Application.ScreenUpdating = True
MsgBox "ok!", 64
End Sub |
评分
-
1
查看全部评分
-
|