vba 将多个 Excel 工作簿合并为一个包含多张工作表的工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28160286/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Combine Multiple Excel Workbooks into one Workbook with multiple sheets
提问by Josiah Hulsey
I have about 70 different excel files that I need to combine into one master workbook. I would like each excel file to get its own worksheet in the master workbook. The name of the worksheet generated in the master workbook doesn't matter.
我有大约 70 个不同的 excel 文件，需要将它们合并到一个主工作簿中。我希望每个 excel 文件在主工作簿中都有自己的工作表。主工作簿中生成的工作表的名称无关紧要。
I retrieved this code off of another website, but cannot make it work for my needs. This code stipulates that all files to be combined are located in the same directory. I have them located here "C:\Users\josiahh\Desktop\cf"
我从另一个网站上检索了此代码，但无法满足我的需要。这段代码规定所有要合并的文件都在同一个目录下。我把它们放在“C:\Users\josiahh\Desktop\cf”
Below is the code as it is now
下面是现在的代码
Sub GetSheets()
Path = "C:\Users\dt\Desktop\dt kte\"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub
采纳答案by David Zemens
This is tested and works as expected. You would be wise to use Option Explicitand declare your variables appropriately in the future, although that did not cause any problems with your code.
这是经过测试并按预期工作的。Option Explicit将来适当地使用和声明变量是明智之举，尽管这不会对您的代码造成任何问题。
As indicated in comments above, the likely failure is that the argument you're passing to the Dirfunction is unnecessarily restrictive:
如上面的评论所示，可能的失败是您传递给Dir函数的参数具有不必要的限制性：
=Dir(path & "*.xls")will look ONLY for files ending exactly in ".xls", and will not account for newer file formats. To resolve that, do =Dir(path & "*.xls*")
=Dir(path & "*.xls")将只查找完全以“.xls”结尾的文件，并且不会考虑较新的文件格式。要解决这个问题，请执行=Dir(path & "*.xls*")
Code below:
代码如下：
Option Explicit
Const path As String = "C:\Users\dt\Desktop\dt kte\"
Sub GetSheets()
Dim FileName As String
Dim wb As Workbook
Dim sheet As Worksheet
FileName = Dir(path & "*.xls*")
Do While FileName <> ""
Set wb = Workbooks.Open(FileName:=path & FileName, ReadOnly:=True)
For Each sheet In wb.Sheets
sheet.Copy After:=ThisWorkbook.Sheets(1)
Next sheet
wb.Close
FileName = Dir()
Loop
End Sub
