|
Sub text()
Dim wb As Workbook, ws As Worksheet
Dim folderPath As String, fileName As String
Dim queue As Object, folder As Variant
Set queue = CreateObject("System.Collections.Queue")
Application.ScreenUpdating = False
' 设置当前工作簿和工作表
Set wb = ThisWorkbook
Set ws = wb.Sheets(1)
' 获取当前工作簿所在的文件夹路径并加入队列
folderPath = wb.Path
Call queue.Enqueue(folderPath)
Do While queue.Count > 0
' 从队列中取出一个文件夹路径
folder = queue.Dequeue()
' 遍历文件夹中的所有文件和子文件夹
fileName = Dir(folder & "\*", vbNormal Or vbReadOnly Or vbHidden Or vbSystem Or vbDirectory)
Do While fileName <> ""
If (GetAttr(folder & "\" & fileName) And vbDirectory) = vbDirectory Then
' 如果是文件夹,则加入队列
If fileName <> "." And fileName <> ".." Then
Call queue.Enqueue(folder & "\" & fileName)
End If
Else
' 如果是文件,汇总数据
If LCase(Right(fileName, 4)) = ".xls" Or LCase(Right(fileName, 5)) = ".xlsx" Then
' 打开文件并复制数据
Workbooks.Open folder & "\" & fileName
With ActiveWorkbook
With .Worksheets(1)
If Not .UsedRange Is Nothing Then
' 找到汇总工作簿的最后一行
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
Dim h '活动工作表的最大行号
h = Cells(Rows.Count, 1).End(xlUp).Row
' 复制数据到汇总工作簿
.Range("b2:BP" & h).Copy Destination:=ws.Cells(lastRow, 1)
End If
End With
.Close SaveChanges:=False
End With
End If
End If
fileName = Dir() ' 获取下一个文件名
Loop
Loop
Application.ScreenUpdating = True
MsgBox "数据汇总完成!"
End Sub |
|