☰

将多个 excel 文件中的数据合并到单个 excel 文件中

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 vba microsoft-excel-2007
将多个 excel 文件中的数据合并到单个 excel 文件中

我需要使用 VBA 将 100 多个 Excel 文件中的数据合并到单个文件中，其中：

我的存储库（D:\rep）包含 100 多个文件，名称为（file1、file2......等），每个 excel 文件由 18 行组成，这些行在所有 excel 文件中都是固定的，并且具有不同的列数，以下是我的 excel 文件的示例

Excel 文件示例

我需要将所有这些文件中的数据合并到一个文件中，并按如下方式合并数据：

新文件中的行仍然相同，第 1 列是（文件 1）列，其值指的是行值的总和，如下图所示

合并文件示例

答案1

如果 Excel 2010 版本包含正确的选项，那么有一种比 VBA 更简单、更好的方法......

我还没有在 Excel 2010 中使用过这个功能，但 PowerQuery 应该可以作为 Microsoft 的免费插件使用。如果 2010 插件版本与 PowerQuery 的当前版本类似，您可以轻松创建一个查询，从文件夹中加载所有或部分文件并合并它们。

PowerQuery 是 Excel 的一个点击式 ETL（提取、转换和加载）工具，它相当易于使用，而且功能极其强大。

在 VBA 中执行此操作要复杂得多。

不过，微软提供了一些可以作为起点的示例代码

Sub MergeAllWorkbooks()
    Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim NRow As Long
    Dim FileName As String
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range

    ' Create a new workbook and set a variable to the first sheet. 
    Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

    ' Modify this folder path to point to the files you want to use.
    FolderPath = "C:\Users\Peter\invoices\"

    ' NRow keeps track of where to insert new rows in the destination workbook.
    NRow = 1

    ' Call Dir the first time, pointing it to all Excel files in the folder path.
    FileName = Dir(FolderPath & "*.xl*")

    ' Loop until Dir returns an empty string.
    Do While FileName <> ""
        ' Open a workbook in the folder
        Set WorkBk = Workbooks.Open(FolderPath & FileName)

        ' Set the cell in column A to be the file name.
        SummarySheet.Range("A" & NRow).Value = FileName

        ' Set the source range to be A9 through C9.
        ' Modify this range for your workbooks. 
        ' It can span multiple rows.
        Set SourceRange = WorkBk.Worksheets(1).Range("A9:C9")

        ' Set the destination range to start at column B and 
        ' be the same size as the source range.
        Set DestRange = SummarySheet.Range("B" & NRow)
        Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
           SourceRange.Columns.Count)

        ' Copy over the values from the source to the destination.
        DestRange.Value = SourceRange.Value

        ' Increase NRow so that we know where to copy data next.
        NRow = NRow + DestRange.Rows.Count

        ' Close the source workbook without saving changes.
        WorkBk.Close savechanges:=False

        ' Use Dir to get the next file name.
        FileName = Dir()
    Loop

    ' Call AutoFit on the destination sheet so that all 
    ' data is readable.
    SummarySheet.Columns.AutoFit
End Sub

看这里了解详细信息。

Stack 上已经有一个例子：

https://stackoverflow.com/questions/31313377/excel-vba-combine-multiple-workbooks-into-one-workbook

相关内容