How to wait until ActiveWorkbook.RefreshAll finishes before executing more code

by

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.

More Related Contents:

  1. How to find the days difference between two dates [closed]
  2. Excel matching based on name date and copying data to another sheet
  3. Get all dates between 2 dates in vba
  4. Different languages issue when inserting formula from VBA
  5. vba: get unique values from array
  6. Unprotect VBProject from VB code
  7. Can VBA Reach Across Instances of Excel?
  8. Excel VBA – Delete empty rows
  9. Efficient way to delete entire row if cell doesn’t contain ‘@’ [duplicate]
  10. Building Undo Into an Excel VBA Macro
  11. Excel vba add code to sheet module programmatically
  12. Best workaround for VBA Transpose array length limit?
  13. How to Lock the data in a cell in excel using vba
  14. How to use Events with Option Button Controls on Userform [duplicate]
  15. How do I use variables to set properties in VBA (Excel)
  16. VBA: Else without If Error
  17. Workbooks.Open Method in VBA
  18. Windows().Activate works on every computer except one
  19. How to highlight a cell using the hex color value within the cell?
  20. Convert RTF (Rich Text Format) code into plain text in Excel
  21. lastrow and excel table.
  22. Open a workbook from VBA and disable Workbook_Open() code?
  23. Creating form programmatically in the module using vba
  24. How to filter listbox values based on a Textbox value
  25. Find and FindNext for Excel VBA
  26. How do I get a list of unique values from a range in Excel VBA?
  27. How to get selected value in multicolumn listbox
  28. Query my excel worksheet with VBA
  29. Delete Hidden/Invisible Rows after Autofilter Excel VBA
  30. How to copy only a single worksheet to another workbook using vba

Leave a Comment