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
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.
Hope this helps!!
