Force Macro to wait till Refreshall is done
 

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

--
NCSU_madma
-----------------------------------------------------------------------
NCSU_madman's Profile: http://www.excelforum.com/member.php...fo&userid=1631
View this thread: http://www.excelforum.com/showthread.php?threadid=27702

Force Macro to wait till Refreshall is done
 

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

-----Original Message-----

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 next
command. I get a msg box that pops up writes "This
action will cancel
a pending Refresh Data Command. Continue?"

So if I say "yes" the data does not refresh but finishes
macro but if
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!


--
NCSU_madman
----------------------------------------------------------
--------------
NCSU_madman's Profile:
http://www.excelforum.com/member.php?
action=getinfo&userid=16312
View this thread:
http://www.excelforum.com/showthread...hreadid=277025

.

Force Macro to wait till Refreshall is done
 

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

-----Original Message-----

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 next
command. I get a msg box that pops up writes "This
action will cancel
a pending Refresh Data Command. Continue?"

So if I say "yes" the data does not refresh but finishes
macro but if
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!


--
NCSU_madman
----------------------------------------------------------
--------------
NCSU_madman's Profile:
http://www.excelforum.com/member.php?
action=getinfo&userid=16312
View this thread:
http://www.excelforum.com/showthread...hreadid=277025

.

Force Macro to wait till Refreshall is done
 

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]


NCSU_madman wrote:
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 next
command. I get a msg box that pops up writes "This action will cancel
a pending Refresh Data Command. Continue?"

So if I say "yes" the data does not refresh but finishes macro but if
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!