Se7enSquared
10/18/2019 - 11:36 PM

Refresh ALL Power Query using VBA

Refreshes all queries in the workbook and then waits for it to finish using Do Events between each one. Uses LogErrorMessageToXls for error logging

Refresh Power Query.vbs
content_copyfile_download
Public Sub RefreshPowerQueries()
    On Error GoTo catch_error:
    
    Dim cn As Object
    For Each cn In ThisWorkbook.Connections
        cn.Refresh
        DoEvents
    Next cn
    
    Exit Sub
    
catch_error:
    If testing_mode = True Then GoTo debug_error
    Libraries.LogErrorMessageToXls "Refreshing Power Queries has failed in " & ThisWorkbook.Name, "Macro error: " & Err.Description & " in RefreshPowerQueries subroutine", "https://hp.sharepoint.com/teams/WS OEM PRGM/OCMS Reports/AutomationErrorLogs.xlsx", "ErrorLog", "tbl_ErrorLog"
    ThisWorkbook.Close
debug_error:
    Application.DisplayAlerts = True
End Sub