|
发表于 2015-11-30 10:39
|
显示全部楼层
本楼为最佳答案
- Sub 导入文件()
- Application.ScreenUpdating = False
- Dim Filename, wb As Workbook, Sht As Worksheet
- Filename = Dir(ThisWorkbook.Path & "\*.xls")
- Set d = CreateObject("scripting.dictionary")
- Do While Filename <> ""
- If Filename <> ThisWorkbook.Name Then
- fn = ThisWorkbook.Path & "" & Filename
- Set wb = Workbooks.Open(fn)
- Set Sht = wb.Worksheets("班级分册")
- r = Sht.[r65536].End(3).Row '数据最大行
- c = [a1].CurrentRegion.Columns.Count '成绩最大列
- arr = Sht.Range("a1:r" & r)
- For i = 3 To UBound(arr)
- xh = arr(i, 18) 'r列学号
- If Not IsError(xh) Then
- If Len(xh) > 0 Then
- For j = 2 To c
- d(xh & arr(2, j)) = arr(i, j) 'd(学号&科目)=成绩
- Next
- End If
- End If
- Next
- wb.Close False
- End If
- Filename = Dir
- Loop
- With ActiveSheet
- r = .[a65536].End(3).Row
- arr = .Range("a1:p" & r)
- For i = 6 To UBound(arr)
- xh = arr(i, 1) '学号
- For j = 2 To UBound(arr, 2)
- arr(i, j) = d(xh & arr(5, j)) '成绩=d(学号&科目)
- Next
- Next
- .Range("a1:p" & r) = arr
- End With
- Application.ScreenUpdating = True
- End Sub
复制代码 |
评分
-
查看全部评分
|