如何用VBA自动修改Power Query M代码适配不同用户本地路径？

可以通过VBA结合正则表达式实现自动修改Power Query的数据源路径，无需用户手动编辑M代码。以下是适配你需求的完整解决方案：

完整VBA代码 #

Sub UpdatePQSourcePath()
    Dim pqQuery As WorkbookQuery
    Dim currentFormula As String
    Dim userName As String
    Dim oldPathPattern As String
    Dim newPath As String
    
    ' 替换为你的Power Query查询名称（在「查询和连接」中查看）
    Set pqQuery = ThisWorkbook.Queries("你的查询名称")
    currentFormula = pqQuery.Formula
    
    ' 从指定单元格读取用户名，可自行修改工作表和单元格位置
    userName = ThisWorkbook.Sheets("Sheet1").Range("A1").Value
    
    ' 正则匹配旧路径格式：C:\Users\任意用户名\Documents\source.xml
    oldPathPattern = "C:\\Users\\[^\\]+\\Documents\\source.xml"
    ' 拼接新的数据源路径
    newPath = "C:\Users\" & userName & "\Documents\source.xml"
    
    ' 用正则替换路径
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = oldPathPattern
    regex.Global = True
    
    ' 更新Power Query的M代码
    pqQuery.Formula = regex.Replace(currentFormula, newPath)
    
    ' 自动刷新查询，可选
    ThisWorkbook.RefreshAll
End Sub

代码说明 #

• 指定查询名称：将"你的查询名称"替换为你实际的Power Query名称（在Excel「数据」选项卡的「查询和连接」面板中可找到）。
• 读取用户名：代码默认从Sheet1的A1单元格读取用户名，你可以调整为用户方便输入的单元格（比如专门的设置工作表）。
• 正则路径匹配：使用正则表达式精准匹配C:\Users\XXX\Documents\source.xml格式的旧路径，不管原路径中的用户名是什么，都能被正确识别并替换，避免了原Split方法可能出现的误替换问题。
• 自动刷新：最后一行的RefreshAll会自动刷新所有查询，用户无需手动操作。

使用步骤 #

1. 在Excel中设置一个单元格让用户输入自己的Windows用户名（对应C:\Users\下的文件夹名称）。
2. 按Alt+F11打开VBA编辑器，右键点击工作簿→「插入」→「模块」，粘贴上述代码。
3. 修改代码中的查询名称和单元格位置，匹配你的实际文件结构。
4. （可选）在Excel界面添加宏按钮：点击「开发工具」→「插入」→「按钮（窗体控件）」，关联此宏，用户点击按钮即可完成路径更新和数据刷新。

注意事项 #

• 确保用户输入的是正确的Windows用户名，否则路径会无效。
• 共享文件需要用户启用宏才能运行此代码。
• 代码使用后期绑定正则对象，无需额外引用库，兼容性更强。

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