delay a VBA to wait for refresh all to finish? autocalcs turning back on too soon

[bauerbach]

I have a super simple macro, infact, its only purpose is to turn off autocalcs, refresh all, and then turn autocalcs back on.

problem is, while the query tables are still refreshing, it finishes the macro, which means it recalcs all the table updates... Completely missing the point of the whole thing.

Is there a way to pause the macro until the queries are finished running?

 

[jscranton]

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit;">Application.Wait(Now + #0:00:01#)</code></pre>

 

[bauerbach]

that can work, no way to make it intelligent to know when the queries are done?

this will be better than nothing atleast. Thanks

edit: or maybe it is doing that... awesome if it is! wont know for sure till I can refresh things again tomorrow morning.

 

[FormR]

In the connection properties for each query table, you can try un-checking the option "Enable background refresh"

 

[Peter_SSs]

Try putting the following line between the Refresh land turning the calculation back to automatic

DoEvents

 

[G_Sereez]

Do this at the top of the macro.

Do Until Application.CalculationState = xlDone
    ActiveWorkbook.RefreshAll
    Loop