将多个Excel工作簿合并到一个包含多个工作表的工作簿中 [英] Combine Multiple Excel Workbooks into one Workbook with multiple sheets

问题描述

我需要将大约70个不同的excel文件合并到一个主工作簿中.我希望每个excel文件都能在主工作簿中获得自己的工作表.在主工作簿中生成的工作表的名称无关紧要.

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.

我从另一个网站上检索了此代码，但无法使其满足我的需要.此代码规定要合并的所有文件都位于同一目录中.我将它们放在这里"C:\ Users \ josiahh \ Desktop \ cf"

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"

下面是现在的代码

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

推荐答案

这已经过测试，可以正常工作.明智的做法是使用Option Explicit并在以后适当地声明变量，尽管这不会对代码造成任何问题.

This is tested and works as expected. You would be wise to use Option Explicit and declare your variables appropriately in the future, although that did not cause any problems with your code.

如上面的注释所示，可能的失败是您传递给Dir函数的参数受到了不必要的限制:

As indicated in comments above, the likely failure is that the argument you're passing to the Dir function is unnecessarily restrictive:

=Dir(path & "*.xls")仅查找完全以".xls"结尾的文件，而不考虑较新的文件格式.要解决此问题，请执行=Dir(path & "*.xls*")

=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*")

下面的代码:

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

这篇关于将多个Excel工作簿合并到一个包含多个工作表的工作簿中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！