Force Macro to wait till Refreshall is done

[NCSU_madman]

Here is my code:

ActiveWorkbook.RefreshAll 'runs query from access and updates in excel
ActiveSheet.Calculate
ActiveWorkbook.Save 'where the macro fails
ActiveWorkbook.Close
-----------------------------

Problem is that the Refreshall does not complete and goes to the nex
command. I get a msg box that pops up writes "This action will cance
a pending Refresh Data Command. Continue?"

So if I say "yes" the data does not refresh but finishes macro but i
say "no" the macro does not finish.

I tried to enter a wait code:

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 30
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

but this does not help. Anyone have any suggestions?

Thanks

 

[DMoney]

Here is one method if you know approximately how long u
need to wait. This code will allow calculations to
continue but pauses everything esle -- the example below
is for 1 minute 10 seconds.

newHour = Hour(Now())
newMinute = Minute(Now()) + 1
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

 

[DMoney]

Here is one method if you know approximately how long u
need to wait. This code will allow calculations to
continue but pauses everything esle -- the example below
is for 1 minute 10 seconds.

newHour = Hour(Now())
newMinute = Minute(Now()) + 1
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

 

[Jake Marx]

Hi NCSU_madman,

There are a few ways to accomplish this without using a timer. First, you
could set the BackgroundQuery property of your QueryTable to False. That
will force synchronous refreshes (your code will wait for them).

If you want to maintain background refreshing of your queries, you could use
WithEvents to sink the events of the QueryTable (one of the events available
for this object is AfterRefresh. This involves using a Class Module and
WithEvents. Chip Pearson has a good explanation of how to use these on his
site www.cpearson.com.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[TarkaDhall]

Configuring DataLinks to wait force macro to wait for refresh to complete.

In order to get this to work for me. I turned off the Enable Background Refresh and Refresh data when opening the file options. These are found in data link properties. My macro then ran through correctly waiting for the data refresh command to finish and I did not get errors about a refresh already running.

Sub Auto_Open()
'
' Auto_Open Macro
'
'
ActiveWorkbook.RefreshAll
'
End Sub