Excel教程:将指定目录中所有Excel文件中的工作表合并到一个文件中

本文为《别怕,Excel VBA其实特别简单(第3版)》随书问题参考答案

如果保存Excel文件的目录为D:\VBA其实特别简单\,可以使用下面的过程:

Sub 合并工作表()

Application.ScreenUpdating = False

Dim FileName As String, Sht As Worksheet, Wb As Workbook

FileName = Dir("D:\VBA其实特别简单\*.xls?")

Do While FileName ""

Workbooks.Open FileName:="D:\VBA其实特别简单\" & FileName

Set Wb = ActiveWorkbook

For Each Sht In Wb.Worksheets

Sht.Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

Next Sht

Wb.Close savechanges:=False

FileName = Dir

Loop

Application.ScreenUpdating = True

End Sub

AI助手