|
Sub 导入()
On Error Resume Next
Application.ScreenUpdating = False
Dim D, Ar, Br, Cr, f(), Dr
Dim R%, C%, X%, Y%, X1%, K%, I%, Co%, T
Dim MyName$
Dim Wb As Workbook
T = Timer
清除
Set D = CreateObject("scripting.dictionary")
MyName = Dir(ThisWorkbook.Path & "\", vbDirectory)
Do While MyName <> ""
If InStr(MyName, ".xlsx") > 0 Then
K = K + 1
ReDim Preserve f(1 To K)
f(K) = ThisWorkbook.Path & "\" & MyName
End If
MyName = Dir
Loop
With ActiveSheet
.Cells.Interior.ColorIndex = xlNone
C = .Cells(2, Columns.Count).End(xlToLeft).Column
Ar = .Range("A1").CurrentRegion
For X = 3 To UBound(Ar)
D(Ar(X, 1) & Ar(X, 2) & Ar(X, 3) & Ar(X, 4)) = X
Next X
For Y = 5 To C Step 5
For I = 1 To UBound(f)
ReDim Cr(1 To 1000, 1 To 5)
K = 0
If InStr(f(I), Ar(1, Y)) > 0 Then
Set Wb = Workbooks.Open(f(I))
Br = Wb.Sheets(1).Range("A1").CurrentRegion
For X1 = 2 To UBound(Br)
If D.Exists(Br(X1, 2) & Br(X1, 3) & Br(X1, 4) & Br(X1, 5)) Then
Co = D(Br(X1, 2) & Br(X1, 3) & Br(X1, 4) & Br(X1, 5))
Ar(Co, Y) = Br(X1, 2)
Ar(Co, Y + 1) = Br(X1, 3)
Ar(Co, Y + 2) = Br(X1, 4)
Ar(Co, Y + 3) = Br(X1, 5)
Ar(Co, Y + 4) = Br(X1, 6)
Else
K = K + 1
Cr(K, 1) = Br(X1, 2)
Cr(K, 2) = Br(X1, 3)
Cr(K, 3) = Br(X1, 4)
Cr(K, 4) = Br(X1, 5)
Cr(K, 5) = Br(X1, 6)
End If
Next X1
End If
Wb.Close
With .Cells(1, 1).Resize(UBound(Ar), UBound(Ar, 2))
.Value = Ar
With .Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
End With
If K > 0 Then
.Cells(UBound(Ar) + 1, Y).Resize(K, 5) = Cr
.Cells(UBound(Ar) + 1, Y).Resize(K, 5).Interior.ColorIndex = 35
End If
Next I
Next Y
End With
MsgBox Format(Timer - T, "0.00")
Set Wb = Nothing
Application.ScreenUpdating = True
End Sub |
|