|
請測試看看,謝謝
Sub test()
Dim Arr, xD, Brr(1 To 200, 1 To 15), a$, a1$, fs, f, fc, f1
Dim n%, m%, T$, DP%, i&, j%
Application.ScreenUpdating = False: Application.DisplayAlerts = False
Set xD = CreateObject("Scripting.Dictionary")
Set fs = CreateObject("Scripting.FileSystemObject")
a = ThisWorkbook.Path: Tm = Timer
Set f = fs.GetFolder(a): Set fc = f.Files
For Each f1 In fc
a1 = f1.Name: If InStr(a1, "~") Then GoTo 97
If InStr(a1, ThisWorkbook.Name) Then GoTo 97
With Workbooks.Open(f1.Path)
Arr = Sheets(1).[a2].CurrentRegion
.Close
End With
If InStr(a1, "后勤") Then
DP = 8
ElseIf InStr(a1, "膳食") Then DP = 9
ElseIf InStr(a1, "医养服务部") Then DP = 10
ElseIf InStr(a1, "财务部") Then DP = 11
ElseIf InStr(a1, "仓库") Then DP = 12
ElseIf InStr(a1, "品质客服部") Then DP = 13
ElseIf InStr(a1, "综合办公室") Then DP = 14
End If
For i = 3 To UBound(Arr)
T = Arr(i, 4): If T = "" Then GoTo 96
If xD.Exists(T) Then
m = xD(T): Brr(m, DP) = Arr(i, 10): Brr(m, 15) = Brr(m, 15) + Arr(i, 10)
If Arr(i, 12) <> "" Then
If Brr(m, 7) = "" Then
Brr(m, 7) = Arr(1, 7) & ":" & Arr(i, 12)
Else
Brr(m, 7) = Brr(m, 7) & " ; " & Arr(1, 7) & ":" & Arr(i, 12)
End If
End If
Else
n = n + 1: xD(T) = n: Brr(n, 1) = n
For j = 2 To 6: Brr(n, j) = Arr(i, j): Next
If Arr(i, 12) <> "" Then Brr(n, 7) = Arr(1, 7) & ":" & Arr(i, 12)
Brr(n, DP) = Arr(i, 10): Brr(n, 15) = Arr(i, 10)
End If
96: Next
97: Next
With Sheets(1)
.[a1].CurrentRegion.Offset(3, 0).ClearContents
If n > 0 Then .[a4].Resize(n, 15) = Brr
End With
MsgBox Timer - Tm
Set fs = Nothing: Set f = Nothing: Set fc = Nothing
Application.ScreenUpdating = True: Application.DisplayAlerts = True
End Sub
|
-
|