|
Option Explicit
Sub 汇总()
On Error Resume Next '// 发生错误,自动执行下一句,就是忽略错误
Application.ScreenUpdating = False '//关闭屏幕刷新
Application.DisplayAlerts = False '//关闭系统提示
Application.EnableEvents = False '//禁止触发其他事件
Application.StatusBar = False '关闭系统状态条
Application.Interactive = False '禁用鼠标、键盘,防干扰
Dim Tim
Tim = Timer
Dim MyFile As Object
Dim Arr, ArrName()
Dim Rc%, Co%, K%
Dim FileName$, Str$
Dim Wb As Workbook
Dim Rg As Range
Set MyFile = CreateObject("scripting.filesystemobject")
FileName = Dir(ThisWorkbook.Path & "\*.xlsx", 16)
Do While FileName <> ""
K = K + 1
ReDim Preserve ArrName(1 To K)
ArrName(K) = ThisWorkbook.Path & "\" & FileName
FileName = Dir
Loop
With ThisWorkbook.Sheets("汇总明细")
.UsedRange.Clear
End With
For K = 1 To UBound(ArrName)
Set Wb = Workbooks.Open(ArrName(K))
With Wb.Sheets(1)
Rc = .Cells(Rows.Count, 1).End(xlUp).Row
Co = .Cells(3, Columns.Count).End(xlToLeft).Column
Str = Left(Wb.Name, 3)
If K = 1 Then
.Range("A1").Resize(Rc - 5, Co).Copy ThisWorkbook.Sheets("汇总明细").Range("B1")
ThisWorkbook.Sheets("汇总明细").Range("B1").Resize(Rc - 5, Co).Value = .Range("A1").Resize(Rc - 5, Co).Value
With ThisWorkbook.Sheets("汇总明细")
.Range("A2") = "编号"
.Range("A2:A3").Merge
.Range("A4") = Str
.Range("A4").Resize(Rc - 8, 1).Merge
End With
Else
Set Rg = ThisWorkbook.Sheets("汇总明细").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
Rg.Offset(0, -1).Resize(Rc - 8, 1).Merge
Rg.Offset(0, -1).Resize(Rc - 8, 1) = Str
.Range("A4").Resize(Rc - 8, Co).Copy Rg
Rg.Resize(Rc - 8, Co).Value = .Range("A4").Resize(Rc - 8, Co).Value
Set Rg = Nothing
End If
Wb.Close False
End With
Next K
Set MyFile = Nothing
Set Wb = Nothing
Application.StatusBar = True '恢复系统状态条
Application.EnableEvents = True '//恢复触发其他事件
Application.ScreenUpdating = True '//恢复屏幕刷新
Application.DisplayAlerts = True '//恢复系统提示
Application.Interactive = True '启用鼠标键盘
MsgBox Format(Timer - Tim, "0.00")
End Sub
Sub 清除()
Sheets("汇总明细").UsedRange.Clear
End Sub |
|