vba 将多个 Excel 工作簿合并为一个工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26455076/
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 a single workbook
提问by Jay C
I am a novice at Visual Basic. I can use either Excel 2010 or Excel 2013 for this task.
我是 Visual Basic 的新手。我可以使用 Excel 2010 或 Excel 2013 来完成这项任务。
I have dozens of workbooks with data on the first worksheet of each. For example One.xlsx, Two.xlsx, Three.xlsx, Four.xlsx each contain information on their respective Sheet1.
我有几十个工作簿，每个工作簿的第一个工作表上都有数据。例如 One.xlsx、Two.xlsx、Three.xlsx、Four.xlsx 每个都包含其各自 Sheet1 上的信息。
I need the information on Sheet1 from each workbook to be combined into a single workbook with sheets that are named from the file name of the original workbook. So for example combined.xlsx would have 4 sheets named One, Two, Three, Four. In every case all information on the underlying worksheets should be copied and combined in the new Workbook as shown below.
我需要将每个工作簿中 Sheet1 上的信息合并到一个工作簿中，这些工作簿的工作表以原始工作簿的文件名命名。例如，combined.xlsx 将有 4 张名为一、二、三、四的工作表。在每种情况下，底层工作表上的所有信息都应复制并合并到新工作簿中，如下所示。
- The Format I need
- 我需要的格式
I found this Macro / Add-In online that gets me close to what I need using the open files add in choice.
我在网上找到了这个宏/插件，它使我接近使用打开文件插件选项所需的内容。
http://www.excelbee.com/merge-excel-sheets-2010-2007-2013#close
http://www.excelbee.com/merge-excel-sheets-2010-2007-2013#close
The Open Files Add-In successfully allows me to aggregate the various Workbook's worksheets into a single workbook. However the tabs are not named from the name of the original file.
Open Files Add-In 成功地允许我将各种工作簿的工作表聚合到一个工作簿中。但是，选项卡不是根据原始文件的名称命名的。
- Correct aggregation of sheets, but incorrect worksheet names.
- 正确聚合工作表，但不正确的工作表名称。
For now all the underlying Workbooks will be in the same folder. The ability to browse and select the files would be nice if this ever changes but if that is too difficult, just indicating the directory path in the Visual Basic code would work. As far as the resultant combined output probably ought to be a new workbook, the filename of the new workbook isn't that important. It could be called combined.xlsx for example.
现在所有底层工作簿都将位于同一个文件夹中。如果这发生变化，浏览和选择文件的能力会很好，但如果这太困难，只需在 Visual Basic 代码中指示目录路径即可。至于最终的组合输出可能应该是一个新工作簿，新工作簿的文件名并不那么重要。例如，它可以被称为combined.xlsx。
回答by Jay C
The following accomplishes the task.
下面完成任务。
Option Explicit
Private Sub CommandButton1_Click()
Dim directory As String, fileName As String, sheet As Worksheet, total As Integer
Dim WrdArray() As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
directory = "c:\test\"
fileName = Dir(directory & "*.xl??")
Do While fileName <> ""
Workbooks.Open (directory & fileName)
WrdArray() = Split(fileName, ".")
For Each sheet In Workbooks(fileName).Worksheets
Workbooks(fileName).ActiveSheet.Name = WrdArray(0)
total = Workbooks("import-sheets.xlsm").Worksheets.Count
Workbooks(fileName).Worksheets(sheet.Name).Copy after:=Workbooks("import-sheets.xlsm").Worksheets(total)
GoTo exitFor:
Next sheet
exitFor:
Workbooks(fileName).Close
fileName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
回答by xmojmr
In Excel press Alt+F11, this will open the Excel VBA editor.
在 Excel 中按Alt+F11，这将打开 Excel VBA 编辑器。
Article http://www.excel-spreadsheet.com/vba/debugging.htmexplains some basics how to use it.
文章http://www.excel-spreadsheet.com/vba/debugging.htm解释了如何使用它的一些基础知识。
In Module1there are 2 short subroutines opensheetsand mergecontaining ~50 lines of code.
在Module1有2个短子程序opensheets和merge代码包含〜50行。
Use F1with cursor within words you don't understand, to learn what it means.
F1在您不理解的单词中使用光标，了解其含义。
Once you understand what the code does, you can tailor it to your needs.
一旦你理解了代码的作用，你就可以根据自己的需要定制它。
