Wait until ActiveWorkbook.RefreshAll finishes - VBA

Solution 1:

I had the same issue, however DoEvents 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 works just fine for me;

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
        objConnection.Refresh

        'Set background-refresh value back to original value
        objConnection.OLEDBConnection.BackgroundQuery = bBackground
    Next

    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.

EDIT: Just saw your edit about using an xlConnectionTypeXMLMAP connection which does not have a BackgroundQuery option, sorry. I'll leave the above for anyone (like me) looking for a way to refresh OLEDBConnection types.

Solution 2:

Though @Wayne G. Dunn has given in code. Here is the place when you don't want to code. And uncheck to disable the background refresh.