|
本帖最后由 ykcbf1100 于 2024-4-22 12:53 编辑
参与一下。。。
- Sub ykcbf() '//2024.4.22
- Set fso = CreateObject("scripting.filesystemobject")
- Set d = CreateObject("Scripting.Dictionary")
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Dim tm: tm = Timer
- Set sh = ThisWorkbook.Sheets("Sheet1")
- bt = [{"账期", "品类", "暂估数", "实际数", "金额", "订单数"}]
- p = ThisWorkbook.Path & ""
- For Each f In fso.GetFolder(p).Files
- If f.Name Like "*.xls*" Then
- If InStr(f.Name, ThisWorkbook.Name) = 0 Then
- fn = fso.GetBaseName(f)
- Set wb = Workbooks.Open(f, 0)
- With wb.Sheets(1)
- r = .Cells(Rows.Count, 1).End(3).Row
- c = .UsedRange.Columns.Count
- arr = .[a1].Resize(r, c)
- wb.Close False
- End With
- For j = 3 To 6
- For i = 2 To UBound(arr)
- s = arr(1, j): ss = Format(arr(i, 2), "yyyy年m月") & "|" & arr(i, j)
- If Not d.exists(s) Then Set d(s) = CreateObject("Scripting.Dictionary")
- If Not d(s).exists(ss) Then
-
- d(s)(ss) = Array(arr(i, 7), arr(i, 8), arr(i, 9), 1)
- Else
- t = d(s)(ss)
- t(0) = t(0) + arr(i, 7)
- t(1) = t(1) + arr(i, 8)
- t(2) = t(2) + arr(i, 9)
- t(3) = t(3) + 1
- d(s)(ss) = t
- End If
- Next
- Next
- End If
- End If
- Next f
- c = 1
- With sh
- .Cells.Clear
- For Each k In d.keys
- m = 1
- ReDim brr(1 To 10000, 1 To 6)
- For n = 1 To 6
- brr(1, n) = bt(n)
- Next
- brr(1, 2) = k
- For Each kk In d(k).keys
- b = Split(kk, "|")
- t = d(k)(kk)
- m = m + 1
- brr(m, 1) = b(0)
- brr(m, 2) = b(1)
- For x = 3 To 6
- brr(m, x) = t(x - 3)
- Next
- Next
- .Cells(1, c).Resize(1, 6).Interior.Color = 49407
- With .Cells(1, c).Resize(m, 6)
- .Value = brr
- .Borders.LineStyle = 1
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- End With
- c = c + 7
- Next
- End With
- Set d = Nothing
- Application.ScreenUpdating = True
- MsgBox "共用时:" & Format(Timer - tm) & "秒!"
- End Sub
复制代码
|
|