Comment wait until ActiveWorkbook.RefreshAll finishes avant executing more code

Microsoft 365 & Office Excel & VBA
1

I have a subroutine that calls ActiveWorkbook.RefreshAll 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 RefreshAll 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 Application.Wait and the Sleep 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 RefreshAll, which gives me the idea of implementing a second flow to be executed afterwards, but that’s not a good workaround.

According to http://www.mrexcel.com/forum/excel-questions/564959-execute-code-after-data-connection-refresh-finished.html, XML connections do not have a BackgroundQuery boolean. That option is only available for ODBC and OLEDB connections, which are types xlConnectionTypeODBC and xlConnectionTypeOLEDB, respectively. The XML connection I am using is of type xlConnectionTypeXMLMAP.

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):

With ActiveWorkbook.Connections("XMLTable")
        .Name = "XMLTable"
        .Description = ""
End With
ActiveWorkbook.Connections("XMLTable").refresh

The class ActiveWorkbook.Connections does NOT have a BackgroundQuery option so that I can set it to False.

How can I force my subsequent code to wait until RefreshAll finishes?

2

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

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