|
- Dim arr(), s&
- Sub 提取()
- Dim wb As Workbook, brr, crr, d, d2
- Set d = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- w = Array("第一单元测试", "第二单元测试", "第一阶段月考", "第三单元测试", "第四单元测试", "第二阶段月考", "期中测试", "第五单元测试", "第六单元测试", "第三阶段月考", "第七单元测试", "第八单元测试", "第四阶段月考", "第一次期末测试", "第二次期末测试", "期末测试")
- s = 0: [e1] = "顺序": n = 1
- ReDim arr(1 To 1000, 1 To 1)
- Zdir ThisWorkbook.Path & "\单元成绩" '递归搜索文件
- Application.ScreenUpdating = False
- Range("a2:d65536").ClearContents
- crr = [a1:e60000]
- For i = 0 To UBound(w) '自定义排序
- d2(w(i)) = i
- Next
- For i = 3 To UBound(crr, 2) - 1 '学科所在的列
- d2(crr(1, i)) = i
- Next
- For i = 1 To s
- Set wb = GetObject(arr(i, 1))
- zf2 = Split(arr(i, 1), "") '学科
- zf = Replace(wb.Name, ".xls", "") '考试名称
- n2 = d2(zf2(UBound(zf2) - 1)) '定位学科所在的列
- n3 = d2(zf) '考试名称对应的序号
- brr = wb.Sheets(1).Range("a1").CurrentRegion
- For j = 2 To UBound(brr)
- z = zf & "," & brr(j, 1)
- If Not d.Exists(z) Then
- n = n + 1
- d(z) = n
- crr(n, 1) = zf
- crr(n, 2) = brr(j, 1)
- crr(n, n2) = brr(j, 2)
- crr(n, 5) = n3
- Else
- crr(d(z), n2) = brr(j, 2)
- End If
- Next
- wb.Close 0
- Next
- With Range("a1").Resize(n, 5)
- .Value = crr
- .Sort [e2], Header:=xlGuess
- End With
- Columns(5).Clear
- Application.ScreenUpdating = True
- End Sub
- Sub Zdir(P)
- Set fs = CreateObject("scripting.filesystemobject")
- For Each f In fs.GetFolder(P).Files
- s = s + 1
- arr(s, 1) = f
- Next
- For Each m In fs.GetFolder(P).SubFolders
- Zdir m
- Next
- End Sub
复制代码 |
|