Excel vba refresh wait

Question

I am creating some code where I can click on a single button and it will refresh the querytables that I have on that sheet.

Now, my problem is that I have more code after the fresh that copies some of the information, but this code is being run right after the refresh has started and the information has not yet been replaced.

I want to create a waiting period for the refresh to complete and then the rest of the code can continue.

I don't want to just wait for 5 seconds but for the refreshing period, so that I am not waiting too long or too short, depending on Internet speed etc.

How can I do this?

Edit:

Simple code:

ActiveWorkbook.RefreshAll 

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

MsgBox("The Refreshing is Completed!") 

Something in that direction. But it can't say the msgbox before it is actually finished.... Sometimes depending on internet speed the refreshing takes shorter or longer, so I want it to be a variable of the actual refreshing time.

Answer 1

I was working with a PowerPivot model, and I wanted to Refresh the data before I saved and closed the Model. However, excel just closed the model before the refresh was complete, and the model resumed refreshing on opening.

Adding the following line right after the RefreshAll method, did the trick:

ThisWorkbook.RefreshAll Application.CalculateUntilAsyncQueriesDone 

I hope it works for you too.

Make sure that you Disable Events to speed things up.

Note that I am using Excel 2010, I am not sure if this method is available in older versions.