让 Excel VBA 等待 PowerQuery 数据刷新以继续 [英] Have Excel VBA wait for PowerQuery data refresh to continue

问题描述

我的场景:我有一些通过 PowerQuery 拉取的数据表，我想自动刷新数据、保存和关闭这些数据表.我每天凌晨 1 点运行这些任务调度程序.问题是 Excel VBA 在进入下一步(保存)之前不会等待 PowerQuery 更新.

My scenario : I have a few data tables pulled via PowerQuery that I wanted to have automatically refresh the data, save, and close. I had a task scheduler run these every day at 1 AM. The problem was that Excel VBA doesn't wait for the PowerQuery to update before it goes to the next step (save).

有很多关于这个的博客，我没有找到任何答案 - 但它让我找到了一些对我有用的东西！我不为代码感到自豪，但它是:

There are a LOT of blogs about this, I didn't find any answer - but it led me to something that worked for me! I'm not proud of the code but here it is:

     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 最终弄清楚了我的意图.

I think this works because I gave excel something to do other than the data refresh, and the extra lines between DoEvents and my next step seemed to make VBA finally figure out what I was intending.

希望这有帮助！！

推荐答案

 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 VBA 等待 PowerQuery 数据刷新以继续的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！