|
Sub a()
Dim arr, i&, j%, m&, d, wb As Workbook, mb As Workbook
Dim myf$, brr(1 To 999999, 1 To 7), r%, mr%
myf = Dir(ThisWorkbook.Path & "\数据表\*.xlsx")
Set d = CreateObject("Scripting.Dictionary")
Set mb = ThisWorkbook
mb.Sheets(1).[a1].CurrentRegion.Offset(1, 0).ClearContents
Application.ScreenUpdating = False
Do While myf <> ""
Set wb = Workbooks.Open(ThisWorkbook.Path & "\数据表\" & myf)
arr = wb.Sheets(1).[a1].CurrentRegion
r = wb.Sheets(1).[b99999].End(3).Row
mr = mb.Sheets(1).[b9999].End(3).Row
wb.Sheets(1).Range("a3:d" & r).Copy
mb.Sheets(1).[b1].Offset(mr, 0).PasteSpecial Paste:=xlPasteValues
mb.Sheets(1).[a1].Offset(mr, 0).Resize(r - 2, 1) = wb.Sheets(1).[a1]
Application.CutCopyMode = False
For i = 3 To UBound(arr) - 1
For j = 5 To UBound(arr, 2)
m = m + 1
brr(m, 1) = arr(1, 1)
brr(m, 2) = arr(i, 1)
brr(m, 3) = DateSerial(Left(arr(i, 2), 4), Mid(arr(i, 2), 6, 2), Right(arr(i, 2), 2))
brr(m, 4) = arr(i, 3)
brr(m, 5) = arr(i, 4)
brr(m, 6) = arr(2, j)
brr(m, 7) = arr(i, j)
d(brr(m, 1) & brr(m, 2) & brr(m, 3) & brr(m, 4) & brr(m, 5) & brr(m, 6)) = brr(m, 7)
Next
Next
wb.Close 0
myf = Dir()
Loop
Sheet1.Activate
arr = [a1].CurrentRegion
For i = 2 To UBound(arr)
For j = 6 To UBound(arr, 2)
arr(i, j) = d(arr(i, 1) & arr(i, 2) & arr(i, 3) & arr(i, 4) & arr(i, 5) & arr(1, j))
Next
Next
[a1].Resize(UBound(arr), UBound(arr, 2)) = arr
Set d = Nothing
Application.ScreenUpdating = True
End Sub |
|