|
本帖最后由 opel-wong 于 2019-10-16 15:52 编辑
代码如下:
- Sub text()
- Dim wb As Workbook, sht As Worksheet, mYp$, mYn$, i%, j%, Rol%, Col$, sName$, mRow%, bm$, mCol%, mStr$
- Application.DisplayAlerts = False: Application.ScreenUpdating = False
- Dim d As Object: Set d = CreateObject("scripting.dictionary")
- Dim T1 As Date: T1 = Timer ' 记时
- Dim TheBook As Workbook: Set TheBook = ThisWorkbook
- Dim TheSheet As Worksheet: Set TheSheet = TheBook.Sheets("程序")
- With TheSheet
- sName = .Range("b3").Value ' 抓取数据表名称
- Col = .Range("b4").Value ' 数据所在列
- Rol = .Range("b5").Value ' 数据起始行
- mCol = .Columns(Col).Column ' 转换列号
- End With
- mYp = TheBook.Path & "\"
- mYn = Dir(mYp & "*.xls*")
- Do While mYn <> ""
- If mYn <> ThisWorkbook.Name Then
- Set wb = GetObject(mYp & mYn) ' Set wb = Workbooks.Open(mYp & mYn)
- bm = Mid(wb.Name, 1, InStrRev(wb.Name, ".") - 1) ' 表名,不带尾缀
- For Each sht In wb.Sheets
- If sht.Name = sName Then ' 如果指定工作表存在
- With sht
- mRow = .Cells(.Rows.Count, 1).End(3).Row ' 打开的表,A列最大行号
- arr = .Range(.Cells(7, 1), .Cells(mRow - 1, mCol)).Value ' 打开的表,赋值到数组
- End With
- For i = 1 To UBound(arr)
- If arr(i, 1) <> "" Then
- mStr = arr(i, 1) & "," & bm
- If Not d.Exists(mStr) Then d(mStr) = Val(arr(i, mCol)) Else d(mStr) = d(mStr) + Val(arr(i, mCol))
- End If
- Next
- End If
- Next
- wb.Close False
- End If
- mYn = Dir()
- Loop
- With TheBook.Worksheets("模板")
- .Activate
- mRow = .Cells(.Rows.Count, 1).End(3).Row
- .Range("B2:F" & mRow).ClearContents
- arr = .Range("A1:F" & mRow).Value
- For i = 2 To UBound(arr)
- For j = 2 To UBound(arr, 2)
- If arr(i, 1) <> "" Then If d.Exists(arr(i, 1) & "," & arr(1, j)) Then arr(i, j) = d(arr(i, 1) & "," & arr(1, j))
- Next
- Next
- .Range("A1:F" & mRow).Value = arr
- End With
- Set d = Nothing: Set TheSheet = Nothing: Set TheBook = Nothing: Set wb = Nothing
- Application.DisplayAlerts = True: Application.ScreenUpdating = True
- MsgBox "操作已完成,用时约:" & Format(Timer - T1, "0.0") & " 秒. ", 64 + 0, "提醒"
- End Sub
复制代码
|
|