Excel VBA Refresh Wait

[ployo60]

I am trying to create a button with a macro code. When the button is pressed it will run a refresh code for a webpage. One of the sheets is updated with the information from this website. There is a waiting period for the refreshing. When done refreshing the rest of the code is run. My problem is the waiting part. Depending on my location and internet speed it sometimes takes 2 seconds to load and sometimes 8 seconds. I want create a timer that will look at when excel is refreshing, wait till its done and then run the rest of the code. See below for a small exable.

Simple code:

ActiveWorkbook.RefreshAll

Here I need the delay or waiting code till all the refreshing is finished... Then

MsgBox("The Refreshing is Completed!")

Hope anyone can help!!

 

[Firefly2012]

Hi

It would be easier to simply disable backgrund refresh of the query (if you do this, when you trigger the refresh, your code will wait until the refresh has completed before executing the subsequent lines). Do this via selecting one of the cells in your external data range and in 2007+ hit the Properties button on the Data ribbon (or in 2003 right-click the mouse and select Properties - I think, can't quite remember).

Now, uncheck the box for Enable Background Refresh. Save the workbook to fix the property. Then you can simply have your subsequent code immediately following the RefreshAll line.

 

[ployo60]

Perfect Answer!

Thank you!

 

[GCWesq]

Perfect indeed! I have been trying for months to figure out how to do this. I had two files that had to be refreshed - one worked and the other didn't. I couldn't work out why, or what to do. This solved it for me. That box was checked in the one that didn't work, and not in the one that did.
I registered with this forum just so I could say thank you to Firefly2012.
Thank you FireFly2012!!!