Refresh All and then Message stating Complete

[johnny51981]

Hi Again!
What is the best way to use the ActiveWorkbook.RefreshAll VBA and then provide a Message Box stating that the RefreshAll is complete?

I have had my queries refresh in a particular order, only because I am in need of a message of its completion, there is really no other reason for me to do this and I would like to increase its refresh speed (turning the Background Refresh back on).

I've tried adding DoEvents in the VBA line following the RefreshAll, but it still tries to bring up the message box before all queries are complete.

Any thoughts?
Here is a sample code of what I am currently using. Thank you in advance!

Sub RefreshData()

    Dim answer1 As Integer
    answer1 = MsgBox("Would you like to refresh all the Data?", vbQuestion + vbYesNo + vbDefaultButton2, "Refresh All Data")
    If answer1 = vbYes Then
    
'    ActiveWorkbook.Connections("Query - Query Seq 1").Refresh
'    ActiveWorkbook.Connections("Query - Query Seq 2").Refresh
'    ActiveWorkbook.Connections("Query - Query Seq 3").Refresh
'    ActiveWorkbook.Connections("Query - Query Seq 4").Refresh
'    ActiveWorkbook.Connections("Query - Query Seq 5").Refresh
'    ActiveWorkbook.Connections("Query - Query Seq 6").Refresh
    
    ActiveWorkbook.RefreshAll
    DoEvents
        
    Call VarianceCheck
    
    answer1 = MsgBox("Data Refresh Successfull!", vbOKOnly)
    Else: answer1 = MsgBox("Data Refresh Cancelled!", vbOKOnly)
    End
    End If
End Sub

Sub VarianceCheck()
    
    myMetric = Range("Variance")
    If myMetric = "YES" Then
    
    answer1 = MsgBox("There is a Variance in the report's data.", vbOKOnly)
    End
    
    Else: ActiveWorkbook.Save
    
    End If
    
End Sub

 

[Matt Allington]

Very difficult. Refresh all operates as a background query. You may be able to change that with code - I’m not sure. If you refresh one of the tables, you can definitely turn off background query with an optional parameter. I don’t know the syntax