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?

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" wrote:


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?




--
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.

Just by changing the query properties to not enable background refresh solved the problem without adding any lines to the macro.

Thanks for your help.

"Dave H" wrote:


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?




--
Dave H

I HAVE SEVERAL FRIENDS THAT MAKE MACRO PROGRAMS USED ON INLINE POKER
GAMING--VERY SIMPLE TO PAUSE IT--JUST CLICK PAUSE BUTTON ON UPPER RIGHT HAND
OF YOUR KEYBOARD--ALSO TRY F12-- OR F13 BUTTONS--USUALLY A PROGRAMMER WILL
USE A F# TO START THE MACRO AND/OR TO STOP IT--GL--ANYTHING ELSE ABOUT
MACROS, JUST GIVE ME A

"Dave H" wrote:


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?




--
Dave H