如何通过VBA自动化更新Power Query数据源以新增工作表至Master表

嘿，这个痛点我太懂了——每次新增工作表都要手动折腾Power Query的数据源，重复操作真的磨人！下面给你一套完整的VBA方案，能自动把所有新增的非Master工作表纳入合并范围，彻底解放双手：

核心思路 #

Power Query的合并逻辑本质是靠M语言定义的，我们可以用VBA自动扫描工作簿里除了'Master'之外的所有工作表，动态生成对应的M数据源代码，然后更新到你的合并查询里，最后一键刷新Master表。

完整VBA代码 #

把这段代码复制到Excel的VBA模块里（按Alt+F11打开编辑器，插入模块后粘贴）：

Sub AutoUpdatePowerQueryMerge()
    Dim wb As Workbook
    Dim qry As WorkbookQuery
    Dim ws As Worksheet
    Dim dataSources As String
    Dim mCode As String
    
    Set wb = ThisWorkbook
    
    ' 1. 构建所有非Master工作表的数据源字符串（M语言格式）
    dataSources = ""
    For Each ws In wb.Worksheets
        If ws.Name <> "Master" Then
            ' 处理工作表名称含特殊字符的情况，用#包裹
            If dataSources <> "" Then dataSources = dataSources & "," & vbCrLf
            dataSources = dataSources & "    Source = Excel.CurrentWorkbook(){[Name=""" & ws.Name & """]}[Content]," & vbCrLf
            dataSources = dataSources & "    #" & Chr(34) & "添加工作表名称" & Chr(34) & " = Table.AddColumn(Source, " & Chr(34) & "来源工作表" & Chr(34) & ", each """ & ws.Name & """)," & vbCrLf
            dataSources = dataSources & "    #" & Chr(34) & "更改类型" & Chr(34) & " = Table.TransformColumnTypes(#" & Chr(34) & "添加工作表名称" & Chr(34) & ", Table.ColumnTypes(Source))"
        End If
    Next ws
    
    ' 2. 生成完整的M语言代码
    mCode = "let" & vbCrLf & _
            "    合并数据源 = {" & vbCrLf & dataSources & vbCrLf & _
            "    }," & vbCrLf & _
            "    合并表 = Table.Combine(合并数据源)" & vbCrLf & _
            "in" & vbCrLf & _
            "    合并表"
    
    ' 3. 找到你的Power Query合并查询（假设查询名为"MergeAllSheets"，记得改成你实际的查询名）
    On Error Resume Next
    Set qry = wb.Queries("MergeAllSheets")
    On Error GoTo 0
    
    If qry Is Nothing Then
        MsgBox "未找到名为'MergeAllSheets'的查询，请检查查询名称！", vbExclamation
        Exit Sub
    End If
    
    ' 4. 更新查询的M代码并刷新
    qry.Formula = mCode
    wb.RefreshAll
    
    MsgBox "已自动更新数据源并刷新Master表！", vbInformation
End Sub

关键细节说明 #

• 查询名称修改：代码里的MergeAllSheets要改成你当前Power Query合并查询的实际名称（在Power Query编辑器的"查询"面板里查看）
• 工作表格式兼容：确保所有新增的工作表和现有表的列名、数据类型完全一致，不然合并会出现错误
• 特殊字符处理：代码里已经处理了工作表名称含空格或特殊字符的情况，用引号和特定语法包裹，避免M语言报错

使用方法 #

1. 按Alt+F11打开VBA编辑器，插入一个新模块，粘贴上面的代码
2. 修改代码里的查询名称为你自己的合并查询名
3. 可以直接在编辑器里按F5执行，或者给Excel界面加个按钮（开发工具→插入→按钮，关联这个宏）
4. 也可以设置成工作簿打开时自动执行，只需在ThisWorkbook模块里添加：

Private Sub Workbook_Open()
    AutoUpdatePowerQueryMerge
End Sub

这样以后每次新增工作表，只要运行这个宏，或者打开工作簿，就能自动把新表的数据合并到Master里啦！

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