将多个 Excel 工作簿合并为一个主工作簿

Question

如上文标题所述。我一直在寻找方法。 我已经尝试了使用 VB 脚本将 excel 工作簿合并到一个 excel 工作簿中的方法。但是，在我编写脚本时，没有将所有工作簿编译到主工作簿中的迹象。

这是我完成的 VB 脚本：

Sub GetSheets()
Path = "C:Users..."
Filename = Dir(Path & ".xlsx") 
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=This.Workbook.Sheets(1)
Next Sheet 
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub

Answer 1

当您打开工作簿时，它将成为活动工作簿。

Sub GetSheets()
Path = "C:Users..."
Filename = Dir(Path & "*.xlsx")
Dim Wb As Workbook
Dim Wb0 As Workbook: Set Wb0 = ActiveWorkbook
Dim Sh As Worksheet
Do While Filename <> ""
Set Wb = Workbooks.Open(Path & Filename)
For Each Sh In Wb.Sheets
Sh.Copy After:=Wb0.Sheets(Wb0.Sheets.Count)
Next Sh
Wb.Saved = True
Wb.Close
Filename = Dir()
Loop
End Sub