Power Query批量刷新持续失败问题及解决方法

我编写了一个VBA宏用于刷新一系列Power Query连接（同一数据源的不同查询，需以完全不同的方式处理数据），但持续遇到部分下载失败的问题（提示“Download did not complete”）。每次失败的查询都不相同，但队列靠后的查询失败概率更高。

我已将查询改为按顺序执行而非并行，但刷新仍会失败。不过手动刷新失败的查询时，它们能正常更新。请问如何让所有查询每次都能成功下载？

原有宏代码 #

Sub RefreshData()
Call RunBillable
 Application.CalculateUntilAsyncQueriesDone
ThisWorkbook.Save
Call RunNonBillable
 Application.CalculateUntilAsyncQueriesDone
ThisWorkbook.Save
Call RunPCH
 Application.CalculateUntilAsyncQueriesDone
ThisWorkbook.Save
End Sub

Sub RunBillable()
 Application.EnableEvents = False
 On Error GoTo skip
  ThisWorkbook.Connections("Query - Total Billable Hours").Refresh
 Done:
  Exit Sub
 skip:
  On Error GoTo -1
 Application.EnableEvents = True
 End Sub

Sub RunNonBillable()
 Application.EnableEvents = False
 On Error GoTo skip
 ThisWorkbook.Connections("Query - NonBillable Hours Details").Refresh
 Done:
  Exit Sub
 skip:
  On Error GoTo -1
 Application.EnableEvents = True
End Sub

Sub RunPCH()
 Application.EnableEvents = False
 On Error GoTo skip
 ThisWorkbook.Connections("Query - PC HOURS").Refresh
 Done:
  Exit Sub
 skip:
  On Error GoTo -1
 Application.EnableEvents = True
End Sub

解决方案：添加重试机制与延迟 #

通过为每个查询设置重试循环，并在重试间隔添加固定延迟，解决了随机下载失败的问题。当某次刷新失败时，会自动重试最多5次，每次重试前等待10秒，确保数据源有足够响应时间。

最终代码如下：

Sub RefreshData()
  RetryRefresh ("Query - Total Billable Hours")
  RetryRefresh ("Query - NonBillable Hours Details")
  RetryRefresh ("Query - PC HOURS")
End Sub

Sub RetryRefresh(query As String)
 Dim retryCount As Integer
 For retryCount = 1 To 5 ' 最多尝试5次刷新
    On Error Resume Next
    ThisWorkbook.Connections(query).Refresh
    If Err.Number <> 0 Then
        Debug.Print "Refresh attempt " & retryCount & " failed"
        Err.Clear
    Else
        Exit For ' 刷新成功则退出循环
    End If
    ' 重试前添加10秒延迟，此步骤为解决问题必需
    Application.Wait (Now + TimeValue("00:00:10"))
Next retryCount
If retryCount > 5 Then
    MsgBox "Refresh of " & query & " failed after multiple attempts."
End If
End Sub

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