按Alt+F11,打开VBA编辑器,插入一个模块,把下面的代码贴进去,按F5执行
Sub 按钮1_Click()
Columns("A:A").Select '清空A列
Selection.ClearContents
Range("A1").Select
myPath = "E:\FILE\" '把文件路径定义给变量,你要汇集的文件夹
n = 1
myFile = Dir(myPath & "\9*.xl*") '9*.xl*是你的文件特征
Do While myFile <> ""
Cells(n, 1) = myFile '列出全部符合要求的文件名
myFile = Dir '找寻下一个文件
n = n + 1
Loop
lastrow = Cells(Rows.Count, 1).End(3).Row '找A列最后一行数
For i = 1 To lastrow
Workbooks("文件搜索.xlsm").Sheets("catalog").Select '文件搜索.xlsm就是本文件名,catalog是本工作表名
m = Cells(i, 1)
ChDir "E:\File"
Workbooks.Open Filename:="E:\File\" & m '打开列表上的文件
Sheets("Sheet1").Select '选择工作表Sheet1
Sheets("Sheet1").Copy After:=Workbooks("文件搜索.xlsm").Sheets(1) '添加到文件搜索.xlsm工作簿中
Workbooks("文件搜索.xlsm").Activate
Sheets("Sheet1").Activate
Sheets("Sheet1").Name = m '修改工作表名为文件名
Workbooks(m).Activate
ActiveWorkbook.Close True '关闭刚打开的文件
Next i ' 处理下一个文件
End Sub
测试通过。