How do I pause a macro?

[Dave H]

I have a spreadsheet that has links to outside sources. I have a macro
that formats the sheet based on the info retrieved. When I try a
refresh all then format. It formats before the new data is received.
It might take 5 seconds or it might take 2 minutes to refresh. Is
there a way to pause a macro until the refresh is complete?

 

[FSt1]

hi
sounds like you have background refresh enabled.
i'm assuming that you are using microsoft query and that you are running a
macro to refresh.
1. Right click the query, click properties. uncheck "enable baground refresh"
2. and this line to the macro
Selection.QueryTable.Refresh BackgroundQuery:=False

this way MSQ wil retrieve all data before the macro starts formating.

Regards
FSt1

 

[Dave H]

I am using microsoft query. I have several queries. I changed the
properties and added the line you suggested but now I am getting a run
time error 1008. Application-defined or object defined error. Where
should this line be placed in my macro? Thanks for your help.

 

[HOBOBOAT]

I have a spreadsheet that has links to outside sources. I have a macro
that formats the sheet based on the info retrieved. When I try a
refresh all then format. It formats before the new data is received.
It might take 5 seconds or it might take 2 minutes to refresh. Is
there a way to pause a macro until the refresh is complete?