Wait until ActiveWorkbook.RefreshAll finishes – VBA

[Originally Posted By]: http://stackoverflow.com/questions/22083668/wait-until-activeworkbook-refreshall-finishes-vba

As commented:

‘~~> your code here before refresh

Activeworkbook.RefreshAll
DoEvents

‘~~> rest of your code here after refresh

What 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.

shareedit

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.

shareedit
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s