将多个 Excel 工作簿合并为一个工作簿 [英] Combine multiple Excel workbooks into a single workbook

问题描述

我是 Visual Basic 的新手.我可以使用 Excel 2010 或 Excel 2013 来完成这项任务.

I am a novice at Visual Basic. I can use either Excel 2010 or Excel 2013 for this task.

我有几十个工作簿，每个工作簿的第一个工作表上都有数据.例如 One.xlsx、Two.xlsx、Three.xlsx、Four.xlsx 均包含各自 Sheet1 上的信息.

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.

我需要将每个工作簿中 Sheet1 上的信息合并到一个工作簿中，其中的工作表以原始工作簿的文件名命名.例如，combined.xlsx 将有 4 张名为一、二、三、四的工作表.在每种情况下，底层工作表上的所有信息都应复制并合并到新工作簿中，如下所示.

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.

• 我需要的格式

我在网上找到了这个宏/插件，使用打开的文件插件选项可以让我接近我需要的东西.

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

打开文件加载项成功地允许我将各种工作簿的工作表聚合到一个工作簿中.但是，选项卡不是根据原始文件的名称命名的.

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.

• 正确的工作表聚合，但不正确的工作表名称.

现在所有底层工作簿都在同一个文件夹中.如果这发生变化，浏览和选择文件的能力会很好，但如果这太困难，只需在 Visual Basic 代码中指示目录路径即可.至于最终的组合输出可能应该是一个新工作簿，新工作簿的文件名并不那么重要.例如可以称为combined.xlsx.

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.

推荐答案

以下完成任务.

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:	est"
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

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