让Excel VBA等待PowerQuery数据刷新继续

Question

我的场景：我有一些通过PowerQuery提取的数据表，我想让它们自动刷新数据、保存和关闭。我有一个任务调度程序每天凌晨1点运行这些程序。问题是Excel VBA没有等到PowerQuery更新后才能进入下一步（保存）。

有很多关于这个的博客，我没有找到任何答案，但它让我找到了对我有用的东西！我并不为代码感到骄傲，但它在这里：

     Public Sub DataRefresh()
DisplayAlerts = False
For Each objConnection In ThisWorkbook.Connections
    'Get current background-refresh value
    bBackground = objConnection.OLEDBConnection.BackgroundQuery
    'Temporarily disable background-refresh
    objConnection.OLEDBConnection.BackgroundQuery = False
    'Refresh this connection
    objConnection.Refresh
    'Set background-refresh value back to original value
    objConnection.OLEDBConnection.BackgroundQuery = bBackground
Next
Workbooks("DA List.xlsm").Model.Refresh
DoEvents
For i = 1 To 100000
Worksheets("DA List").Range("G1") = i
Next i
DoEvents
ActiveWorkbook.Save
Application.Quit
End Sub

我认为这是可行的，因为我给了excel一些除了数据刷新之外的事情，而DoEvents和我的下一步之间的额外行似乎让VBA最终明白了我的意图。

希望这能有所帮助！！

Answer 1

 Public Sub DataRefresh()
DisplayAlerts = False
For Each objConnection In ThisWorkbook.Connections
'Get current background-refresh value
bBackground = objConnection.OLEDBConnection.BackgroundQuery
'Temporarily disable background-refresh
objConnection.OLEDBConnection.BackgroundQuery = False
'Refresh this connection
objConnection.Refresh
'Set background-refresh value back to original value
objConnection.OLEDBConnection.BackgroundQuery = bBackground
Next
Workbooks("DA List.xlsm").Model.Refresh
DoEvents
For i = 1 To 100000
Worksheets("DA List").Range("G1") = i
Next i
DoEvents
ActiveWorkbook.Save
Application.Quit
End Sub