如何通过VBA传递文件路径至Power Query数据源，无需单元格中转？

核心思路 #

通过工作簿自定义文档属性作为VBA与Power Query的中间传递载体，替代原方案中的工作表单元格存储路径。这种方式既避免了对工作表的依赖，又能安全地在VBA和Power Query之间传递文件路径参数。

1. 修改VBA代码（路径选择与属性维护） #

更新后的VBA代码会将用户选择的文件路径存入工作簿的自定义属性（属性不存在则自动创建），无需写入工作表：

Sub FileSelectandRefreshQueries()
    Dim popPath As Variant, clinPath As Variant
    
    ' 选择人口管理CSV文件
    If MsgBox("请在弹出窗口中选择人口管理文件（必须是.csv格式）。", vbOKCancel, "操作提示") = vbOK Then
        popPath = Application.GetOpenFilename("CSV Files (*.csv),*.csv", Title:="选择人口管理文件")
        If popPath <> False Then
            SetOrCreateDocProperty "PopManFilePath", popPath
        End If
    End If
    
    ' 选择临床复杂度CSV文件
    If MsgBox("请在弹出窗口中选择临床复杂度文件（必须是.csv格式）。", vbOKCancel, "操作提示") = vbOK Then
        clinPath = Application.GetOpenFilename("CSV Files (*.csv),*.csv", Title:="选择临床复杂度文件")
        If clinPath <> False Then
            SetOrCreateDocProperty "ClinComFilePath", clinPath
        End If
    End If
    
    ' 刷新所有Power Query查询
    ThisWorkbook.RefreshAll
End Sub

' 辅助函数：创建或更新工作簿自定义属性
Sub SetOrCreateDocProperty(propertyName As String, propertyValue As Variant)
    Dim prop As DocumentProperty
    On Error Resume Next
    Set prop = ThisWorkbook.CustomDocumentProperties(propertyName)
    On Error GoTo 0
    
    If prop Is Nothing Then
        ' 属性不存在时创建新属性
        ThisWorkbook.CustomDocumentProperties.Add _
            Name:=propertyName, _
            LinkToContent:=False, _
            Type:=msoPropertyTypeString, _
            Value:=propertyValue
    Else
        ' 属性已存在时更新值
        prop.Value = propertyValue
    End If
End Sub

2. 修改Power Query代码（读取自定义属性作为数据源） #

以人口管理表格的Power Query为例，修改M代码读取工作簿自定义属性中的路径：

let
    ' 定义自定义函数：读取工作簿指定自定义属性
    GetDocProperty = (propName as text) =>
        let
            Properties = Excel.CurrentWorkbook(){[Name="DocumentProperties"]}[Content],
            FilteredProp = Table.SelectRows(Properties, each [Name] = propName),
            PropValue = if Table.RowCount(FilteredProp) > 0 then FilteredProp{0}[Value] else null
        in
            PropValue,
    ' 获取人口管理文件路径
    LocalPath = GetDocProperty("PopManFilePath"),
    ' 加载CSV数据源（路径不为空时执行）
    Source = if LocalPath <> null then Csv.Document(File.Contents(LocalPath),[Delimiter="#(tab)", Encoding=1200, QuoteStyle=QuoteStyle.None]) else null
in
    Source

临床复杂度表格的Power Query只需将GetDocProperty("PopManFilePath")替换为GetDocProperty("ClinComFilePath")即可。

方案优势 #

• 无工作表依赖：彻底避免了因误编辑单元格导致的路径失效问题
• 数据安全性：自定义属性存储在工作簿内部，不会显示在工作表中，减少误操作风险
• 代码模块化：VBA负责路径选择与属性维护，Power Query专注于数据加载，职责清晰

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