CODEWITHSUNDEEP
excelvbapowerquery
MBrann
MBrann

Reputation: 265

Message box when power query connection refreshes

I am hoping someone can help. I have a simple code which will refresh all power query connections. The issue I am having is having a message box pop up when the query has finished.

Is anyone able to offer any solutions?

So far I have attempted the following

Sub RefreshAttempt1()

ActiveWorkbook.RefreshAll

Sheets("Conc").Range("E2").Value = Format(Now(), "mmm-yy")
Sheets("Conc").Range("E5").Value = Application.UserName
Sheets("Conc").Range("E3").Value = Range("A8").Value & " 0001/" & Format(Now(), "mm")

Msgbox = "Your query has now refreshed"

End Sub

Sub RefreshAttempt2()
   
Application.StatusBar = "DATA REFRESH IN PROGRESS"

ActiveWorkbook.RefreshAll
    
Application.StatusBar = "DATA REFRESH COMPLETE. REFRESHING ALL VIEWS NOW."

Application.OnTime Now() + TimeSerial(0, 0, 5), "clearStatusBar"

End Sub

Private Sub clearStatusBar()
    
Application.StatusBar = False
    
End Sub

Sub RefreshAttempt3()

 Application.StatusBar = "REFRESH IN PROGRESS"
 ActiveWorkbook.RefreshAll
 Application.StatusBar = False
 MsgBox "Refresh Complete   ", , "VivDev"

End Sub

Upvotes: 0

Views: 3504

Answers (2)

MBrann
MBrann

Reputation: 265

I have solved the issue by using the Application.EnableEvents and in the query properties unchecking "Enable background refresh"

Sub RefreshAttempt4()

Application.EnableEvents = False

    ActiveWorkbook.RefreshAll

Application.EnableEvents = True

MsgBox "Refresh is now complete!"

End Sub

Upvotes: 0

Nikola Ganev
Nikola Ganev

Reputation: 36

Can you share what kind of pop up message do you receive? You can try to add the below code in the start of any sub.

Application.DisplayAlerts = False ' stops most of the pop up messages

Just don`t forget to allow the alerts again then the sub is complete by adding the below at the end.

Application.DisplayAlerts = True ' activate the alerts again

Upvotes: 0

Related Questions