使用VBA更新指定Power Query表格时遇运行时错误1004

错误原因 #

• 连接类型不匹配：遍历所有WorkbookConnection时，部分连接（比如Power Query生成的连接）不属于OLEDBConnection类型，直接访问OLEDBConnection.BackgroundQuery会触发1004错误。
• 计时逻辑失效：原代码中endTime = Timer紧跟在startTime之后，导致计算的耗时为0，完全没记录实际刷新时间。
• 重复刷新冗余：代码中同时执行了遍历刷新所有连接、单独刷新指定QueryTable、调用RefreshAll，三次刷新操作重复，逻辑混乱。

修复后的代码 #

Sub Refresh_Specific_Query_With_Timer()
    Dim timerWs As Worksheet
    Dim startTime As Double
    Dim endTime As Double
    Dim elapsedTime As Double
    Dim targetListObj As ListObject
    Dim objConnection As WorkbookConnection
    
    ' 记录开始时间
    startTime = Timer
    
    ' 定位目标Power Query对应的ListObject
    On Error Resume Next
    Set targetListObj = ThisWorkbook.Sheets("2023 YTD_Models").ListObjects("YTD_Models")
    On Error GoTo 0
    
    If targetListObj Is Nothing Then
        MsgBox "未找到指定的查询表YTD_Models", vbExclamation
        Exit Sub
    End If
    
    ' 处理目标连接（避免遍历所有连接报错）
    Set objConnection = targetListObj.QueryTable.WorkbookConnection
    
    ' 判断连接类型，仅处理支持的类型
    Select Case objConnection.Type
        Case xlConnectionTypeOLEDB, xlConnectionTypeText, xlConnectionTypeWeb
            Dim bBackground As Boolean
            ' 保存原有后台刷新设置
            bBackground = objConnection.OLEDBConnection.BackgroundQuery
            ' 临时禁用后台刷新，确保同步完成
            objConnection.OLEDBConnection.BackgroundQuery = False
            ' 刷新连接
            objConnection.Refresh
            ' 恢复原有设置
            objConnection.OLEDBConnection.BackgroundQuery = bBackground
        Case xlConnectionTypeModel ' Power Query连接类型
            ' Power Query连接直接刷新，无需修改后台设置
            objConnection.Refresh
        Case Else
            MsgBox "不支持的连接类型", vbExclamation
    End Select
    
    ' 记录结束时间
    endTime = Timer
    
    ' 计算耗时
    elapsedTime = endTime - startTime
    
    ' 输出耗时到Summary工作表H14
    On Error Resume Next
    Set timerWs = ThisWorkbook.Sheets("Summary")
    On Error GoTo 0
    
    If timerWs Is Nothing Then
        MsgBox "未找到Summary工作表", vbExclamation
        Exit Sub
    End If
    
    timerWs.Range("H14").Value = "刷新耗时: " & Format(elapsedTime, "0.00") & " 秒"
End Sub

关键修复点说明 #

• 连接类型判断：通过objConnection.Type区分连接类型，针对Power Query的xlConnectionTypeModel类型单独处理，避免访问不存在的OLEDBConnection属性。
• 修正计时逻辑：将endTime = Timer移至所有刷新操作完成后，确保计算的是实际刷新耗时。
• 精准刷新目标查询：直接定位到YTD_Models对应的ListObject和连接，避免遍历所有连接导致的错误，同时移除重复刷新操作，提升效率。

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