将多个Excel工作簿合并到单个工作簿中 [英] Combine multiple Excel workbooks into a single workbook

问题描述

我是Visual Basic的新手。我可以使用Excel 2010或Excel 2013来执行此任务。

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

我需要每个工作簿上Sheet1的信息可以使用从原始工作簿的文件名命名的工作表组合成单个工作簿。所以例如combined.xlsx将有4张名为一，二，三，四的表格。在每种情况下，所有关于工作表的信息都应该复制并组合在新的工作簿中，如下所示。

• 我需要的格式
• 
  

我发现这个宏/加载项在线，让我接近我需要使用打开的文件添加选择。

http：//www.excelbee。 com / merge-excel-sheets-2010-2007-2013＃关闭

打开文件加载项成功允许我将各种Workbook的工作表汇总到单一的工作簿。但是，标签不是从原始文件的名称命名的。

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

现在，所有底层的工作簿将在同一个文件夹中。浏览和选择文件的能力将是很好的，如果这有变化，但如果这太难了，只是指示Visual Basic代码中的目录路径将工作。只要结果合并的产出大概应该是一个新的工作簿，新的工作簿的文件名不是那么重要。它可以被称为combine.xlsx。

解决方案

以下功能完成任务。

  Option Explicit 
 
 Private Sub CommandButton1_Click（）
 
 Dim directory As String，fileName As String，sheet As Worksheet ，总As Asteger 
 Dim WrdArray（）As String 
 
 Application.ScreenUpdating = False 
 Application.DisplayAlerts = False 
 
 directory =c：\\ \\ test\
 fileName = Dir（directory&* .xl ??）
 
尽管fileName<> 
 Workbooks.Open（directory& fileName）
 WrdArray（）= Split（fileName，。）
对于工作簿（fileName）中的每个工作表.Worksheets 
工作簿（fileName）.ActiveSheet.Name = WrdArray（0）
 total =工作簿（import-sheets.xlsm）。Worksheets.Count 
工作簿（fileName）.Worksheets（sheet.Name）。 ：=工作簿（import-sheets.xlsm）工作表（总计）
 
转到退出对于
 
下一张单据
 
 exitFor：
工作簿（fileName）.Close 
 fileName = Dir（）
循环
 
 Application.ScreenUpdating = True 
 Application.DisplayAlerts = True 
 
 End Sub 
  

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

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.

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.

• 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

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.

• 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.

解决方案

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

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