Wait until ActiveWorkbook.RefreshAll finishes – VBA

by

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.

More Related Contents:

  1. to make this code simpler
  2. VBA – How to grab the cell below if a cell has text?
  3. VBA code takes long time to execute in excel
  4. Find text and show value if condition is met
  5. How can I insert variable into formula in VBA
  6. Code in VBA loops and never ends. How to fix this?
  7. Excel column number from column name
  8. Pop up the Excel Statusbar?
  9. VBA – destroy a modeless UserForm instance properly
  10. How To Speed Up VBA Code
  11. Execute a command in command prompt using excel VBA
  12. “Unable to get the VLookup property of the WorksheetFunction Class” error [duplicate]
  13. Copy data from another Workbook through VBA
  14. Excel VBA Load worksheet in Function
  15. Searching an excel with two search words (multiple lookup) using vba macro [closed]
  16. Excel VBA – Combine rows with duplicate values in one cell and merge values in other cell
  17. Load csv file into a VBA array rather than Excel Sheet
  18. Insert line break in wrapped cell via code
  19. Change color of certain characters in a cell
  20. VBScript – How to make program wait until process has finished?
  21. Want VBA in excel to read very large CSV and create output file of a small subset of the CSV
  22. Copy filtered data to another sheet using VBA
  23. VBA Macro crashes after 32000 rows
  24. How to create dynamic variable names VBA
  25. How to break a long string into multiple lines
  26. Read VBA macros (or vbaProject.bin) of an Excel file without opening it in MS Excel [closed]
  27. simple vba code gives me run time error 91 object variable or with block not set
  28. How to have vba execute every 10 minutes?
  29. Read and write from/to registry in VBA
  30. Unable to read data from a CSV using ADO due to the driver thinking I am working with integers/number and showing nulls instead of text

Leave a Comment