Power Query Tables won't download data until VBA finishes executing

Question

I have a spreadsheet with 4 worksheets, each one connected to a power query which connects to an OLEDB database and runs a select to import data into the workbook.

I am trying to automate the refresh of the queries.

I have VBA in the workbook which
- disables background refresh,
- runs Workbook.RefreshAll,
- resets the background refresh to true (as I need this for the automation software to be able to run it in the first place) and
- then waits 30 seconds to give the automation software a bit of time before it starts saving the workbook.

All the software does is call the macro in the workbook and then save following execution.

The queries correctly execute one after the other and I can tell the data in the tables has refreshed.

The power query confirmation that the rows have been downloaded happens after the VBA completes executing, so the automation software saves it before this is done and Excel reports the download as having failed. This isn't a problem when everything works, but we won't know when the download has truly failed, unless I add in some sort of a row count comparison which is really a workaround rather than an actual solution.

I have tried creating two subs in the workbook, one to do the refresh and then another which calls the first sub and then waits 30 seconds, I was hoping the download would complete after the first sub finishes executing, but this doesn't happen. I have tried refreshing one connection at a time instead of using RefreshAll, tried refreshing the actual power query tables instead of the connections and have tried refreshing with background query set to true and then looping until the refresh is marked as complete (which works if you step through it, but crashes if you just run the VBA in full).

I also tried saving the spreadsheet at the end of the VBA, but it still waits until the save is complete to update the power query status.

My latest VBA:

Sub Workbook_RefreshAll()

Application.DisplayAlerts = False

For Each objConnection In ThisWorkbook.Connections

    'Temporarily disable background-refresh
    objConnection.OLEDBConnection.BackgroundQuery = False
    objConnection.OLEDBConnection.MaintainConnection = False

Next

ActiveWorkbook.RefreshAll

For Each objConnection In ThisWorkbook.Connections

    'Re-enable background-refresh
    objConnection.OLEDBConnection.BackgroundQuery = True
    objConnection.OLEDBConnection.MaintainConnection = True

Next

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 30
waitTime = TimeSerial(newHour, newMinute, newSecond)

Application.Wait waitTime

End Sub

(I can't remember whether the maintain connection parameter actually helped as I as trying to force the connection to drop after each refresh, but it works so haven't taken it out.)

Jochen Jochen · Accepted Answer · 2017-09-27T11:25:35

Instead of Application.Wait try the following:

Do until now() >= waittime
    DoEvents
Loop

Power Query Tables won't download data until VBA finishes executing

1 Answers