如何实现子Excel工作簿数据自动追加至主追踪表（保留历史）

我有多个模板一致的Excel子工作簿，每个子工作簿录入同类但不同的信息，需要实现：

• 子工作簿录入数据时同步追加到主追踪工作簿，主表保留历史数据
• 子工作簿清空后录入新数据，新数据仍追加到主表，不覆盖原有内容

现有问题：

• 手动复制粘贴效率极低（数据量超10万条）
• 现有VBA代码效率不高，且会复制表头，需优化；同时想了解PowerShell或其他替代方案

现有VBA代码（用户提供）：

Dim ZZZmaster As Worksheet 'ZZZ references the name of the specific sheet to extract
Dim YYYmaster As Worksheet 'YYY references the name of the specic sheet to extract
Dim ZZZtemplates As Worksheet
Dim YYYtemplates As Worksheet
Dim filepath As String
    
    
    If MsgBox("Update ZZZ?", vbYesNo) = vbYes Then

    Set ZZZmaster = ThisWorkbook.Sheets("ZZZ_Bulk")

    
        filepath = Application.GetOpenFilename("Excel Files(*.xls;*.xlsx), *.xls;*.xlsx", , "Select the updated Report")
               If filepath = "False" Then Exit Sub ' User Cancelled

    
    Set ZZZtemplates = Workbooks.Open(filepath).Sheets("ZZZ_Bulk")

    ZZZtemplates.Range("A1").CurrentRegion.Copy Destination:=ZZZmaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        ActiveWorkbook.Close
    
    
    Else
        
        If MsgBox("Update YYY?", vbYesNo) = vbYes Then
        
            Set YYYmaster = ThisWorkbook.Sheets("YYY_Bulk")

    
            filepath = Application.GetOpenFilename("Excel Files(*.xls;*.xlsx), *.xls;*.xlsx", , "Select the updated Report")
               If filepath = "False" Then Exit Sub ' User Cancelled
    
             Set YYYtemplates = Workbooks.Open(filepath).Sheets("ZZZ_Bulk")

                 YYYtemplates.Range("A1").CurrentRegion.Copy Destination:=YYYmaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                      ActiveWorkbook.Close
                      
        Else
            End If
            
    End If
    
End Sub

针对原有代码的问题，优化点包括：跳过表头、提升大数据处理效率、封装重复逻辑减少冗余。

优化后的代码：

Sub UpdateMasterSheets()
    ' 关闭Excel耗时功能，大幅提升处理速度
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    Dim masterSheetName As String
    Dim response As VbMsgBoxResult
    
    ' 询问是否更新ZZZ表
    response = MsgBox("Update ZZZ?", vbYesNo)
    If response = vbYes Then
        masterSheetName = "ZZZ_Bulk"
        Call AppendDataToMaster(masterSheetName)
    Else
        ' 询问是否更新YYY表
        response = MsgBox("Update YYY?", vbYesNo)
        If response = vbYes Then
            masterSheetName = "YYY_Bulk"
            Call AppendDataToMaster(masterSheetName)
        End If
    End If
    
    ' 恢复Excel默认设置
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Private Sub AppendDataToMaster(masterSheetName As String)
    Dim masterWs As Worksheet
    Dim sourceWs As Worksheet
    Dim sourceDataRange As Range
    Dim lastRowMaster As Long
    Dim lastRowSource As Long
    Dim filepath As String
    
    ' 设置主表对象
    Set masterWs = ThisWorkbook.Sheets(masterSheetName)
    
    ' 选择源文件
    filepath = Application.GetOpenFilename("Excel Files(*.xls;*.xlsx), *.xls;*.xlsx", , "Select the updated Report")
    If filepath = "False" Then Exit Sub ' 用户取消选择
    
    ' 只读打开源文件，避免锁定子工作簿
    Set sourceWs = Workbooks.Open(filepath, ReadOnly:=True).Sheets(masterSheetName)
    
    ' 获取源数据最后一行，跳过表头（从第2行开始）
    lastRowSource = sourceWs.Cells(sourceWs.Rows.Count, "A").End(xlUp).Row
    If lastRowSource < 2 Then
        ' 源表无数据，直接关闭
        sourceWs.Parent.Close SaveChanges:=False
        Exit Sub
    End If
    
    ' 定义源数据范围（A2到最后一行的所有列）
    Set sourceDataRange = sourceWs.Range("A2:" & sourceWs.Cells(lastRowSource, sourceWs.Columns.Count).End(xlToLeft).Address)
    
    ' 确定主表追加位置
    lastRowMaster = masterWs.Cells(masterWs.Rows.Count, "A").End(xlUp).Row
    If lastRowMaster = 1 And masterWs.Range("A1").Value = "" Then
        ' 主表为空，从第1行开始粘贴
        sourceDataRange.Copy Destination:=masterWs.Range("A1")
    Else
        ' 主表已有数据，从下一行开始粘贴
        sourceDataRange.Copy Destination:=masterWs.Range("A" & lastRowMaster + 1)
    End If
    
    ' 关闭源文件，不保存
    sourceWs.Parent.Close SaveChanges:=False
End Sub

优化说明：

• 跳过表头：仅复制子工作簿第2行及以后的数据，避免重复表头
• 效率提升：关闭屏幕更新、事件触发和自动计算，处理10万+条数据时速度显著提升
• 只读打开：防止子工作簿被锁定，同时避免误修改
• 空数据判断：子工作簿无数据时直接跳过，避免无效操作

PowerShell无需打开Excel界面，直接读取文件，适合批量处理多个子工作簿，效率优于VBA。

前置准备 #

先安装ImportExcel模块（管理员权限下执行）：

Install-Module -Name ImportExcel

批量处理脚本 #

# 主工作簿路径和目标工作表名
$masterFilePath = "C:\Your\Path\MasterWorkbook.xlsx"
$masterSheetName = "ZZZ_Bulk"

# 子工作簿所在文件夹路径（自动处理该文件夹下所有Excel文件）
$sourceFolder = "C:\Your\Path\SubWorkbooks"

# 获取主表现有数据行数，确定追加起始位置
$masterData = Import-Excel -Path $masterFilePath -WorksheetName $masterSheetName
$startRow = if ($masterData) { $masterData.Count + 1 } else { 1 }

# 遍历文件夹下所有Excel文件
Get-ChildItem -Path $sourceFolder -Filter "*.xlsx" | ForEach-Object {
    # 读取子工作簿数据，跳过表头（从第2行开始）
    $sourceData = Import-Excel -Path $_.FullName -WorksheetName $masterSheetName -StartRow 2
    
    if ($sourceData) {
        # 追加数据到主表
        $sourceData | Export-Excel -Path $masterFilePath -WorksheetName $masterSheetName -StartRow $startRow -Append
        $startRow += $sourceData.Count
        Write-Host "已追加数据：$($_.Name)"
    } else {
        Write-Host "无数据可追加：$($_.Name)"
    }
}

Write-Host "批量处理完成"

使用说明：

• 修改$masterFilePath、$masterSheetName和$sourceFolder为实际路径
• 脚本自动遍历指定文件夹下的所有子工作簿，跳过表头后追加数据到主表
• 无需打开Excel，处理大数据量时速度远快于VBA

适合非编程用户，通过可视化操作实现自动合并、追加数据，支持一键刷新。

操作步骤：

1. 打开主工作簿，点击数据选项卡 → 获取数据 → 从文件 → 从文件夹
2. 选择子工作簿所在文件夹，点击确定
3. 在弹出的对话框中，点击转换数据进入Power Query编辑器
4. 点击添加列 → 自定义列，输入公式：=Excel.CurrentWorkbook(){[Name="ZZZ_Bulk"]}[Content]（替换为你的工作表名）
5. 点击自定义列右侧的展开箭头，选择需要加载的列，取消勾选使用原始列名作为前缀
6. 移除不需要的列（如源文件名、路径等），然后点击移除顶部行（输入1，跳过子工作簿的表头）
7. 点击关闭并上载，选择仅创建连接；右键连接 → 加载到 → 选择主表的起始位置，勾选追加到现有工作表
8. 后续子工作簿更新后，点击数据选项卡 → 全部刷新，即可自动追加新数据

优势：

• 无需编写代码，可视化操作
• 支持批量处理多个子工作簿
• 一键刷新同步数据，适合非技术用户

内容的提问来源于stack exchange，提问作者sjfel