|
没看清,我填的是区间名,你要求的是表名。
- Sub 遍历工作簿()
- Dim strPath As String, strFile As String
- strPath = ThisWorkbook.Path & Application.PathSeparator & "数据文件" & Application.PathSeparator
- strFile = Dir(strPath & "*.xlsx")
- With Application
- .ScreenUpdating = False
- .DisplayAlerts = False
- .EnableEvents = False
- .Calculation = xlCalculationManual
- End With
- Do While Len(strFile)
- If strFile <> ThisWorkbook.Name Then
- Call 汇总(strPath & strFile)
- End If
- strFile = Dir
- Loop
- With Application
- .ScreenUpdating = True
- .DisplayAlerts = True
- .EnableEvents = True
- .Calculation = xlCalculationAutomatic
- End With
- MsgBox "查找结束"
- End Sub
- Sub 汇总(strFullname As String)
- Dim i As Byte, k As Long
- Dim lLastRow As Long, lRow As Long
- Dim objwb As Workbook
- Dim objDic As Object, objDicTemp As Object
- Dim strKey As String, Key, Item
- Dim arr, arrTemp
-
- On Error GoTo ErrorHandler
- Set objwb = GetObject(strFullname)
- Windows(objwb.Name).Visible = True
- Set objDic = CreateObject("scripting.dictionary")
- lRow = 1
- With objwb
- For i = 1 To .Worksheets.Count - 1
- With .Worksheets("sheet" & i)
- lLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
- arr = .Range("a1:p" & lLastRow).Value
- End With
- For k = LBound(arr) To UBound(arr)
- If Not objDic.exists(arr(k, 4)) Then
- objDic.Add arr(k, 4), CreateObject("scripting.dictionary")
- End If
- Set objDicTemp = objDic(arr(k, 4))
- strKey = arr(k, 10) & "#" & arr(k, 11) & "#" & arr(k, 13) & "#" & arr(k, 14) & "#" & arr(k, 16)
- objDicTemp(strKey) = objDicTemp(strKey) & k & ","
- Set objDic(arr(k, 4)) = objDicTemp
- Next
- For Each Key In objDic.keys
- Set objDicTemp = objDic(Key)
- For Each Item In objDicTemp.items
- With .Worksheets("sheet0")
- If (Len(Item) - Len(Replace(Item, ",", ""))) > 1 Then
- arrTemp = Split(Item, ",")
- For k = LBound(arrTemp) To UBound(arrTemp) - 1
- .Cells(lRow, 1).Resize(, UBound(arr, 2)).Value = WorksheetFunction.Index(arr, arrTemp(k), 0)
- lRow = lRow + 1
- Next
- .Cells(lRow - 1, 18).Value = i
- lRow = lRow + 1
- End If
- End With
- Next
- Next
- objDic.RemoveAll
- Next
- .Close True
- End With
- Exit Sub
- ErrorHandler:
- MsgBox Err.Number & vbCrLf & Err.Description, vbCritical + vbOKCancel, IIf(objwb Is Nothing, "", objwb.Name)
- If Not objwb Is Nothing Then
- objwb.Close False
- End If
- End Sub
复制代码 |
|