0

I would like to show Msg Box with "Done" after completing refreshing connection. Unfortunately the message pops up righ after starting macro, when connection is not fully refreshed (it is ODBC connection, it takes up to 15 minutes to fully refresh). Solution with do and without DoEvents ends with the same result. Could you help?

Sub database_refresh()
  Dim cn As WorkbookConnection

  Set cn = ActiveWorkbook.Connections("xxxx")
  cn.Refresh
End Sub

Sub msg_box_dbup()
  MsgBox "Done"
End Sub

Sub database_refresh_event()
  Call database_refresh
  DoEvents
  Call msg_box_dbup
End Sub
Dominique
  • 16,450
  • 15
  • 56
  • 112
Kraysky
  • 67
  • 1
  • 6
  • I'd use only one subroutine with the body of: `ActiveWorkbook.Connections("xxxx").Refresh MsgBox "Done!"` – Maciej Los Aug 10 '21 at 07:19
  • I have already tried this way - it did not work. – Kraysky Aug 10 '21 at 07:29
  • 2
    I can't check at the moment as refreshing a query - if you right-click the connection is there an option for `Enable background refresh`. Make sure it's not ticked. [Refresh an external data connection in Excel](https://support.microsoft.com/en-us/office/refresh-an-external-data-connection-in-excel-1524175f-777a-48fc-8fc7-c8514b984440) – Darren Bartrup-Cook Aug 10 '21 at 07:30
  • "it did not work" is not informative at all. Can you explain in details, what is wrong... – Maciej Los Aug 10 '21 at 07:31
  • @MaciejLos: It means that result is the same as before - msg box pops up before refresh is completed. – Kraysky Aug 10 '21 at 07:33
  • 1
    See if [How to check whether Connection Refresh was successful](https://stackoverflow.com/questions/43548543/how-to-check-whether-connection-refresh-was-successful) helps – Naresh Aug 10 '21 at 07:42

1 Answers1

0

Thank you guys!

Solution with disabling Background Refresh resolved my problem ; )

Kraysky
  • 67
  • 1
  • 6