|
- Sub 导入文件()
- Application.ScreenUpdating = False
- Dim filename, wb As Workbook, Sht As Worksheet
- filename = Dir(ThisWorkbook.Path & "\班级调查表\*.xls")
- Set d = CreateObject("scripting.dictionary")
- Dim brr(1 To 10000, 1 To 1)
- Do While filename <> ""
- If filename <> ThisWorkbook.Name Then
- fn = ThisWorkbook.Path & "\班级调查表" & filename
- Set wb = Workbooks.Open(fn)
- Set Sht = wb.Worksheets(1)
- arr = Sht.[a1:s15]
- bj = Val(Split(filename, "(")(1)) '班级名
- For i = 4 To 14
- For j = 5 To 18
- x = bj & arr(i, 2) & arr(2, j) & arr(i, j) '班级+项目+学科+评价为key
- d(x) = d(x) + 1 '计数
- Next
- Next
- If arr(15, 3) <> "" Then
- n = n + 1
- brr(n, 1) = Split(filename, "班")(0) & "班:" & arr(15, 3) '对老师的建议存入数组brr
- End If
- wb.Close False
- End If
- filename = Dir
- Loop
- Set Sht = Nothing
-
- crr = Sheet1.Range("a1:au231")
- For i = 1 To 217 Step 18
- xk = Mid(crr(i, 1), 9, 2)
- For k = i + 3 To i + 13
- For j = 3 To 47
- bj = IIf(crr(i + 1, j) = "", crr(i + 1, j - 1), crr(i + 1, j)) '班级
- bj = Val(bj)
- x = bj & crr(k, 2) & xk & crr(i + 2, j) '班级+项目+学科+评价
- crr(k, j) = d(x) '填充汇总数
- Next
- Next
- Next
- Sheet1.Range("a1:au231") = crr
- Sheet3.[a20].Resize(n, 1) = brr
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|