VBA+Power Query加载SAP BeX文件时Runtime 1004错误排查请求

• 环境冲突：SAP BeX Analyzer为32位，本地使用64位Office，直接打开BeX生成的.xlsm文件提取数据时触发兼容性错误，且无法通过常规设置屏蔽。
• 替代方案异常：改用Power Query + VBA加载文件中DF_GRID_1数据至工作表，但执行.Refresh BackgroundQuery:=False时偶尔触发Runtime 1004错误，错误提示为Expression.SyntaxError: Token Equal expected，Power Query界面也报相同语法错误。
• 限制条件：源.xlsm文件由RPA自动生成，无法修改其生成逻辑或文件结构。

该语法错误本质是Power Query解析BeX生成的数据源时，遇到不符合M语言语法的表达式，常见触发场景：

1. DF_GRID_1数据结构不稳定：RPA生成文件时，偶尔会出现字段名含特殊字符（空格、等号、括号），或数据单元格内存在不规范内容（未闭合引号、异常等号使用），导致M代码解析时判定缺少预期等号。
2. Power Query连接字符串动态解析失误：VBA中若动态生成连接逻辑，偶尔会因文件路径、表名拼接错误，导致M代码出现语法缺失（如遗漏等号、引号不配对）。
3. 64位Office兼容性bug：32位BeX生成的文件内部OLEDB/ODBC连接信息，在64位Power Query解析时偶尔出现语法转译错误，导致M代码结构异常。

1. 绕开Power Query，用原生Excel对象读取数据 #

直接通过VBA操作Excel对象模型读取DF_GRID_1数据，完全规避32/64位兼容性冲突与Power Query语法解析问题：

Sub LoadDFGridDirectly()
    Dim srcWB As Workbook
    Dim srcWS As Worksheet
    Dim destWS As Worksheet
    Dim dataRange As Range
    
    ' 打开源文件时禁用宏与数据连接，避免兼容性报错
    Set srcWB = Workbooks.Open("\\共享目录\目标文件.xlsm", _
        UpdateLinks:=xlUpdateLinksNever, _
        ReadOnly:=True, _
        IgnoreReadOnlyRecommended:=True, _
        Editable:=False, _
        Notify:=False)
    
    ' 定位DF_GRID_1数据区域（如果是ListObject则改为ListObjects("DF_GRID_1").Range）
    Set srcWS = srcWB.Worksheets("DF_GRID_1")
    Set dataRange = srcWS.UsedRange
    
    ' 复制数据到目标工作表
    Set destWS = ThisWorkbook.Worksheets("目标工作表")
    destWS.Cells.Clear
    dataRange.Copy destWS.Range("A1")
    
    ' 关闭源文件不保存
    srcWB.Close SaveChanges:=False
    Set srcWB = Nothing
End Sub

2. 修复Power Query的M代码语法异常 #

若必须使用Power Query，在VBA中添加错误捕获与M代码修复逻辑，应对偶发的解析错误：

Sub RefreshPQWithSyntaxFix()
    Dim qry As WorkbookQuery
    Dim mCode As String
    
    Set qry = ThisWorkbook.Queries("你的查询名称")
    mCode = qry.Formula
    
    ' 修复字段名未加引号导致的语法错误
    mCode = Replace(mCode, "DF_GRID_1[", "DF_GRID_1[""")
    mCode = Replace(mCode, "]", " ""]")
    
    ' 重新设置查询公式并尝试刷新
    qry.Formula = mCode
    On Error Resume Next
    ThisWorkbook.Connections("你的连接名称").Refresh BackgroundQuery:=False
    
    ' 若仍报错，强制重置查询公式重试
    If Err.Number = 1004 Then
        qry.Formula = "let Source = Excel.Workbook(File.Contents(""\\共享目录\目标文件.xlsm""), null, true), DF_GRID_1_Sheet = Source{[Item=""DF_GRID_1"",Kind=""Sheet""]}[Data] in DF_GRID_1_Sheet"
        ThisWorkbook.Connections("你的连接名称").Refresh BackgroundQuery:=False
    End If
    On Error GoTo 0
End Sub

3. 调整Office基础设置 #

• 打开64位Office的选项->信任中心->信任中心设置->宏设置，勾选信任对VBA工程对象模型的访问（注意安全风险）。
• 在数据->连接->属性中，取消勾选启用后台刷新，强制同步加载，减少异步解析导致的偶发错误。

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