如何通过VBA修改Power Query数据源并实现错误时切换？

核心逻辑 #

通过VBA批量修改Power Query查询的M代码路径，同时添加错误捕获机制：先尝试默认数据源刷新，失败则自动切换到备用路径重新执行刷新。

1. 单查询路径修改函数 #

该函数负责替换指定查询M代码中的目标文件路径：

Sub UpdateQueryFilePath(queryName As String, oldPath As String, newPath As String)
    Dim qry As WorkbookQuery
    Dim mCode As String
    
    Set qry = ThisWorkbook.Queries(queryName)
    mCode = qry.Formula
    
    ' 替换路径（注意转义双引号，匹配M代码的格式）
    mCode = Replace(mCode, "File.Contents(""" & oldPath & """)", "File.Contents(""" & newPath & """)")
    
    qry.Formula = mCode
End Sub

2. 批量修改所有8个查询的路径 #

遍历工作簿内所有查询，统一替换路径：

Sub BatchUpdateAllQueries()
    Const defaultPath As String = "FILE PATH"
    Const backupPath As String = "FILE PATH 2"
    Dim qry As WorkbookQuery
    
    For Each qry In ThisWorkbook.Queries
        UpdateQueryFilePath qry.Name, defaultPath, backupPath
    Next qry
    
    ' 可选：立即刷新所有查询
    ' ThisWorkbook.RefreshAll
End Sub

3. 带错误捕获的自动切换刷新流程 #

实现「默认数据源失败则切换备用源」的完整逻辑：

Sub RefreshWithFallback()
    Const defaultPath As String = "FILE PATH"
    Const backupPath As String = "FILE PATH 2"
    Dim qry As WorkbookQuery
    Dim isRefreshSuccess As Boolean
    
    ' 先恢复默认路径并尝试刷新
    On Error Resume Next
    For Each qry In ThisWorkbook.Queries
        UpdateQueryFilePath qry.Name, backupPath, defaultPath
    Next qry
    ThisWorkbook.RefreshAll
    
    isRefreshSuccess = (Err.Number = 0)
    Err.Clear
    On Error GoTo 0
    
    ' 刷新失败则切换备用路径重新执行
    If Not isRefreshSuccess Then
        For Each qry In ThisWorkbook.Queries
            UpdateQueryFilePath qry.Name, defaultPath, backupPath
        Next qry
        ThisWorkbook.RefreshAll
        MsgBox "默认数据源刷新失败，已切换至备用数据源完成刷新"
    Else
        MsgBox "默认数据源刷新成功"
    End If
End Sub

注意事项 #

• 确保VBA编辑器已启用Microsoft Excel Object Library和Microsoft Office Object Library（默认已启用）。
• 路径替换时必须转义双引号，否则无法匹配M代码的格式。
• 若需筛选特定查询（而非全部），可在遍历循环中添加名称判断逻辑（如If qry.Name Like "主查询*" Then）。
• Power Query会自动处理主查询与依赖查询的刷新顺序，无需额外设置。

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