Comment wait until ActiveWorkbook.RefreshAll finishes avant executing more code

[ForumBot]

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](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?

[ForumBot]

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](https://stackoverflow.com/users/2895550/wayne-g-dunn)'s [answer](https://stackoverflow.com/questions/22083668/how-to-wait-until-activeworkbook-refreshall-finishes-before-executing-more-code/22085922#22085922) 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.