Ce site est optimisé pour être consulté depuis un navigateur moderne dans lequel JavaScript est activé.

Comment wait until ActiveWorkbook.RefreshAll finishes avant executing more code

ayi

I have a subroutine that calls <code>ActiveWorkbook.RefreshAll</code> to bring new data in from an XML source on a website, and then performs multiple modifications to it. The problem is that not enough time is given for the <code>RefreshAll</code> command to finish, so the following subs and functions end up not executing correctly, which result in repeated rows not being correctly erased.
I have tried using <code>Application.Wait</code> and the <code>Sleep</code> function, but they seem to pause the refresh process too. I simply want the rest of the code to wait until the refresh process finishes before executing the rest of the code.
Right now I was only able to fix it by not calling on <code>RefreshAll</code>, which gives me the idea of implementing a second flow to be executed afterwards, but that’s not a good workaround.
According to <a href="http://www.mrexcel.com/forum/excel-questions/564959-execute-code-after-data-connection-refresh-finished.html" rel="noopener nofollow ugc">http://www.mrexcel.com/forum/excel-questions/564959-execute-code-after-data-connection-refresh-finished.html</a>, XML connections do not have a <code>BackgroundQuery</code> boolean. That option is only available for ODBC and OLEDB connections, which are types <code>xlConnectionTypeODBC</code> and <code>xlConnectionTypeOLEDB</code>, respectively. The XML connection I am using is of type <code>xlConnectionTypeXMLMAP</code>.
This code is the macro that is recorded when I do this via the UI (including unchecking the “Enable background refresh” in the table properties):
<pre><code class="lang-auto">With ActiveWorkbook.Connections("XMLTable")
.Name = "XMLTable"
.Description = ""
End With
ActiveWorkbook.Connections("XMLTable").refresh

</code></pre>
The class <code>ActiveWorkbook.Connections</code> does NOT have a <code>BackgroundQuery</code> option so that I can set it to False.
How can I force my subsequent code to wait until <code>RefreshAll</code> finishes?

ayi_2

I had the same issue with an OLEDBConnection connection type, however <code>DoEvents</code> (as suggested in a prior answer) didn’t help me as my data connections had background-refresh enabled. Instead, using <a href="https://stackoverflow.com/users/2895550/wayne-g-dunn">Wayne G. Dunn</a>’s <a href="https://stackoverflow.com/questions/22083668/how-to-wait-until-activeworkbook-refreshall-finishes-before-executing-more-code/22085922#22085922">answer</a> as a jumping-off point, I created the following solution, which worked:
<pre><code class="lang-auto">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

</code></pre>
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 <code>BackgroundQuery</code>, this will not work if you are using an <code>xlConnectionTypeXMLMAP</code> connection, sorry.