[Originally Posted By]: http://stackoverflow.com/questions/22083668/wait-until-activeworkbook-refreshall-finishes-vba
‘~~> your code here before refresh
‘~~> rest of your code here after refresh
DoEvents does is like momentarily allowing Windows to take a break from
Macro to process all pending events before returning to the macro.
So when you refresh your workbook and put
DoEvents after, it will not resume the macro until the refresh is finished.
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;
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"
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.