|
Sub 汇总()
Dim strFileName As String, xlApp, xlBook, xlSheet
Dim i As Integer, arr, temp(1 To 28, 1 To 10), j As Integer, k As Integer
strFileName = Dir(ActiveWorkbook.Path & "\")
i = 1
Set xlApp = CreateObject("Excel.Application")
Application.ScreenUpdating = False
Do While strFileName <> ""
If strFileName <> "汇总.xls" And InStr(strFileName, ".xls") > 0 Then
Set xlBook = xlApp.Workbooks.Open(ActiveWorkbook.Path & "\" & strFileName)
Set xlSheet = xlBook.Worksheets
arr = xlSheet(1).Range("d6:m33")
For j = 1 To 28
For k = 1 To 10
temp(j, k) = temp(j, k) + arr(j, k)
Next
Next
xlApp.DisplayAlerts = False
xlBook.Close
End If
i = i + 1
strFileName = Dir
Loop
xlApp.Quit
Sheets(1).Range("d6:m33") = temp
Application.ScreenUpdating = True
End Sub
- Sub 汇总()
- Dim strFileName As String
- Dim temp(1 To 28, 1 To 10), arr, arrTemp
- Dim i As Integer, j As Integer, k As Integer
- Dim arr2(1 To 3) As Variant
- Dim blCopy As Boolean
-
- For i = 1 To UBound(arr2)
- arr2(i) = temp
- Next
- strFileName = Dir(ActiveWorkbook.Path & "\*.xls")
- With Application
- .ScreenUpdating = False
- .DisplayAlerts = False
- .EnableEvents = False
- .Calculation = xlCalculationManual
- End With
- Do While strFileName <> ""
- If strFileName <> ThisWorkbook.Name Then
- With GetObject(ActiveWorkbook.Path & "" & strFileName)
- For i = 1 To .Worksheets.Count
- If Not blCopy Then
- .Worksheets(i).Copy before:=Worksheets(i)
- End If
- arr = .Worksheets(i).Range("d6:m33")
- arrTemp = arr2(i)
- For j = 1 To 28
- For k = 1 To 10
- arrTemp(j, k) = arrTemp(j, k) + arr(j, k)
- Next
- Next
- arr2(i) = arrTemp
- Next
- Windows(.Name).Visible = True
- blCopy = True
- .Close False
- End With
- End If
- strFileName = Dir
- Loop
- For i = Worksheets.Count To UBound(arr2) + 1 Step -1
- Worksheets(i).Delete
- Next
- For i = 1 To UBound(arr2)
- With Worksheets(i)
- .Range("d6:m33") = arr2(i)
- .Name = "sheet" & i
- End With
- Next
- With Application
- .ScreenUpdating = True
- .DisplayAlerts = True
- .EnableEvents = True
- .Calculation = xlCalculationAutomatic
- End With
- MsgBox "汇总完成", vbInformation
- End Sub
复制代码只要格式相同,都是3个表就成了。
|
|