How to wait until ActiveWorkbook.RefreshAll finishes before executing more code

I had the same issue with an OLEDBConnection connection type, however DoEvents (as suggested in a prior answer) didn’t help me as my data connections had background-refresh enabled. Instead, using Wayne G. Dunn’s answer as a jumping-off point, I created the following solution, which worked:

Sub Refresh_All_Data_Connections()
    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
        'Set background-refresh value back to original value
        objConnection.OLEDBConnection.BackgroundQuery = bBackground
    MsgBox "Finished refreshing all data connections"
End Sub

The MsgBox is for testing only and can be removed once you’re happy the code waits.

Also, I prefer ThisWorkbook to ActiveWorkbook as I know it will target the workbook where the code resides, just in case focus changes. Nine times out of ten this won’t matter, but I like to err on the side of caution.

However, note that because this uses BackgroundQuery, this will not work if you are using an xlConnectionTypeXMLMAP connection, sorry.

Leave a Comment