新建个工作薄 放到所有文件一个文件夹里 Alt+F11 视图 代码窗口
代码丢进去 F5运行
Sub 遍历当前文件夹下工作薄()
Dim i, r, wk As Workbook, sh As Worksheet, rr
t = Timer
Application.ScreenUpdating = False
ActiveSheet.Range("2:60000").ClearContents
p = ThisWorkbook.Path & "\"
s = Dir(p & "*.xls*")
Do While s <> ""
If s <> ThisWorkbook.Name Then
Set wk = Workbooks.Open(p & s)
Set sh = wk.Sheets("Sheet1") '收集Sheet1
r = sh.[a65536].End(3).Row
For i = 2 To r
With ThisWorkbook.Worksheets("sheet1") '收集到Sheet1
rr = ThisWorkbook.Worksheets("sheet1").[a65536].End(xlUp).Row + 1
For j = 1 To 30
Cells(rr, j).Value = sh.Cells(i, j)
Cells(rr, 31) = wk.Name
Next j
End With
Next i
wk.Close False
End If
s = Dir
Loop
t1 = Timer - t
MsgBox ("所有工作薄收集完成,用时" & t1 & "秒")
Application.ScreenUpdating = True
End Sub