如何禁止Excel打开时加载Power Query源（解决文件不存在错误）

问题场景 #

通过VBA的FileDialog选择本地JSON文件，动态更新Power Query公式中的文件路径，但换电脑后因路径不存在弹出以下错误：

[DataSource.Error] Could not find file 'C:\last_loaded_file.json'.
[DataSource.Error] Could not find a part of the path 'C:\folder\folder\...'.

即使禁用了连接的所有刷新复选框，Excel仍会尝试触发Power Query加载。

解决思路 #

1. 在Power Query中添加文件存在性检查 #

修改查询公式，用File.Exists()函数先验证路径有效性，不存在时返回空表而非触发错误：

let
    FilePath = "C:\last_loaded_file.json",
    // 检查文件是否存在
    FileExists = File.Exists(FilePath),
    // 分支处理：存在则加载JSON，否则返回null
    Quote = if FileExists then Json.Document(File.Contents(FilePath)) else null,
    // 不存在时返回空列表，避免后续步骤报错
    orderItems = if FileExists then Quote[orderItems] else {},
    #"Converted to Table" = Table.FromList(orderItems, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    // 后续原有步骤保持不变
    #"Sorted Rows" = ...
in
    #"Sorted Rows"

这样当文件不存在时，查询会生成空表，不会弹出数据源错误提示。

2. 彻底关闭Power Query的自动刷新 #

除了禁用连接的刷新复选框，还需调整全局和查询级别的设置：

• 全局设置：文件 > 选项 > 数据 > 查询加载，取消勾选「打开文件时刷新所有数据连接」
• 查询单独设置：右键目标查询 > 属性，取消勾选「打开文件时刷新」和「后台刷新」

3. 用VBA在打开文件时动态修正路径 #

在Workbook_Open事件中添加代码，检查上次保存的路径是否有效，无效则修改查询公式避免报错：

Private Sub Workbook_Open()
    Dim targetQuery As WorkbookQuery
    Dim savedPath As String
    Dim updatedFormula As String
    
    ' 指定目标查询名称
    Set targetQuery = ThisWorkbook.Queries("YourQueryName")
    ' 从查询公式中提取已保存的文件路径（需根据你的公式结构调整提取逻辑）
    savedPath = "C:\last_loaded_file.json"
    
    ' 检查文件是否存在
    If Dir(savedPath) = "" Then
        ' 将路径替换为占位符，或直接替换为返回null的逻辑
        updatedFormula = Replace(targetQuery.Formula, """C:\last_loaded_file.json""", """C:\placeholder.json""")
        ' 或者直接替换数据源部分为null：
        ' updatedFormula = Replace(targetQuery.Formula, "Json.Document(File.Contents(""C:\last_loaded_file.json""))", "null")
        targetQuery.Formula = updatedFormula
    End If
End Sub

4. 用单元格存储路径，Power Query动态引用 #

将文件路径存储在Excel单元格（比如Sheet1!A1），Power Query直接引用单元格值而非硬编码路径：

let
    ' 引用存储路径的单元格（需先给单元格定义名称为FilePathCell）
    FilePath = Excel.CurrentWorkbook(){[Name="FilePathCell"]}[Content]{0}[Column1],
    FileExists = File.Exists(FilePath),
    Quote = if FileExists then Json.Document(File.Contents(FilePath)) else null,
    orderItems = if FileExists then Quote[orderItems] else {},
    #"Converted to Table" = Table.FromList(orderItems, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    // 后续步骤
    #"Sorted Rows" = ...
in
    #"Sorted Rows"

之后VBA只需更新该单元格的值，无需修改查询公式。打开文件时若单元格路径无效，查询会自动返回空表。

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