|
本帖最后由 ykcbf1100 于 2024-1-1 18:17 编辑
代码更新了- Sub ykcbf() '//2024.1.1
- Dim fns As New Collection
- Set Fso = CreateObject("scripting.filesystemobject")
- Set d = CreateObject("Scripting.Dictionary")
- Set d1 = CreateObject("Scripting.Dictionary")
- Application.ScreenUpdating = False
- Set sh = ThisWorkbook.Sheets("汇总表")
- xx = [{"学籍号","班级","校名","性别"}]
- xm = [{"分数","县名","校名","进步"}]
- p = ThisWorkbook.Path & "\成绩"
- Set ff = Fso.GetFolder(p)
- Call GetFiles(ff, fns, Fso)
- For Each k In fns
- m = m + 1
- sh.Cells(m, 1) = k(0)
- sh.Cells(m, 2) = k(1)
- sh.Cells(m, 3) = Val(k(1))
- Next
- sh.[a1].Resize(fns.Count, 3).Sort sh.[c1], 1
- zrr = sh.[a1].Resize(fns.Count, 3)
- c = 4
- For y = 1 To UBound(zrr)
- n = n + 1
- Set Wb = Workbooks.Open(zrr(y, 1), 0)
- With Wb.Sheets(1)
- arr = .UsedRange
- Wb.Close False
- End With
- For i = 5 To UBound(arr)
- If arr(i, 1) <> Empty Then
- s = CStr(arr(i, 1))
- If Not d1.exists(s) Then
- d1(s) = Array(CStr(arr(i, 1)), CStr(arr(i, 4)), arr(i, 6), arr(i, 7))
- End If
- s = CStr(arr(i, 1)) & "|" & n
- If Not d.exists(s) Then
- d(s) = Array(arr(i, 9), arr(i, 12), arr(i, 13), arr(i, 14), n)
- End If
- End If
- Next
- Next
- t = d.items
- With sh
- .UsedRange.Clear
- .[a1].Resize(1, 4) = xx
- For x = 1 To n
- .Cells(1, c + (x - 1) * 4 + 1).Resize(1, 4) = xm
- Next
- .Columns("A:B").NumberFormatLocal = "@"
- .[a2].Resize(d1.Count, 4) = Application.Rept(d1.items, 1)
- arr = .UsedRange
- For i = 2 To UBound(arr)
- For m = 1 To n
- k = 0
- For x = 1 To 4
- k = c + (m - 1) * 4 + x
- s = CStr(arr(i, 1)) & "|" & m
- If d.exists(s) Then
- arr(i, k) = d(s)(x - 1)
- End If
- Next
- Next
- Next
- .Columns("A:B").NumberFormatLocal = "@"
- With .UsedRange
- .Value = arr
- .Borders.LineStyle = 1
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- End With
- End With
- Set d = Nothing
- Set d1 = Nothing
- Application.ScreenUpdating = True
- MsgBox "OK!"
- End Sub
- Function GetFiles(ff, fns, Fso)
- For Each f In ff.Files
- If f.Name Like "*.xls*" Then
- fns.Add Array(f.Path, Fso.GetBaseName(f))
- End If
- Next
- End Function
复制代码
一下。。。 |
|