I'm working with a user-initiated macro (Excel 2003) that conducts a query
every hour for 72 hours. Is there a way to confirm whether or not this macro
is running without waiting to observe it conduct it's next scheduled query?

You could have the code write a value into a cell on a sheet, such as the
time it ran and check on that from time to time.

You could also add a line of code to write something to the immediate
(debug) window each time it runs the query, something like:
Debug.Print Now()
and then you can later use [Alt]+[F11] to examine the contents of the
Immediate window, which should have a long list (but probably not all 72
entries) of times that the query was run.

Probably the first way is best way. I'd just add a sheet to the workbook
for this purpose only. Let's call it "QueryTimes".

Then in the portion of the code that runs the query, not in any timing loop
in it, I'd put code like this:
Worksheets("QueryTimes").Range("A" & Rows.Count).End(xlUp). _
Offset(1,0) = Now()

That will enter the time the query is run in column A, starting at row 2 and
continuing down that sheet.

"DJ" wrote:

I'm working with a user-initiated macro (Excel 2003) that conducts a query
every hour for 72 hours. Is there a way to confirm whether or not this macro
is running without waiting to observe it conduct it's next scheduled query?

Thanks JLatham!
I see how this lets me know when the last refresh was conducted. My concern
has been that after initiating the 72-hr "refresh schedule" macro, I've been
working on other macros that are called up immediately after each refresh.
Sometimes the changes I make in these referenced macros have errors, and I'm
not sure if these errors can stop the running of the 72-hr macro.

Using now() at the start of each refresh is fine, but still means I have to
wait until the start of the next refresh to confirm the "refresh schedule" is
still working. Maybe the only way around this is to put another loop in the
refresh macro that shows now() more frequently than every hour . . .

I'm new to all this, and I just thought maybe there was a place or way to
see either a progress bar, or a list of running macros the way a list of
running programs can be seen (e.g., Windows Task Manager).


"DJ" wrote:

I'm working with a user-initiated macro (Excel 2003) that conducts a query
every hour for 72 hours. Is there a way to confirm whether or not this macro
is running without waiting to observe it conduct it's next scheduled query?

I'll barge in. You could use the status bar to show which macro is
in-process. Then, if one of the auxilliary macros is shown on the status
bar for an unusually long time, you'd know there's a problem. HTH, James

Sub Refresh ()
application.statusbar="Processing Refresh"
'more code
Call Macro1
application.statusbar="Processing Refresh (M1 complete)
Call Macro2
application.statusbar="Processing Refresh (M2 complete)
'more code
application.statusbar=false
End Sub

Sub Macro1()
application.statusbar="Processing Macro1"
'more code
End Sub

Sub Macro2()
application.statusbar="Processing Macro2"
'more code
End Sub

"DJ" wrote in message
...
Thanks JLatham!
I see how this lets me know when the last refresh was conducted. My
concern
has been that after initiating the 72-hr "refresh schedule" macro, I've
been
working on other macros that are called up immediately after each refresh.
Sometimes the changes I make in these referenced macros have errors, and
I'm
not sure if these errors can stop the running of the 72-hr macro.

Using now() at the start of each refresh is fine, but still means I have
to
wait until the start of the next refresh to confirm the "refresh schedule"
is
still working. Maybe the only way around this is to put another loop in
the
refresh macro that shows now() more frequently than every hour . . .

I'm new to all this, and I just thought maybe there was a place or way to
see either a progress bar, or a list of running macros the way a list of
running programs can be seen (e.g., Windows Task Manager).


"DJ" wrote:

I'm working with a user-initiated macro (Excel 2003) that conducts a
query
every hour for 72 hours. Is there a way to confirm whether or not this
macro
is running without waiting to observe it conduct it's next scheduled
query?

Zone has offered one option - and when combined with another loop in your
code as you thought about, could tell you whether or not it's running. But I
would assume that any code crash is going to bring it all to a grinding halt.

"DJ" wrote:

Thanks JLatham!
I see how this lets me know when the last refresh was conducted. My concern
has been that after initiating the 72-hr "refresh schedule" macro, I've been
working on other macros that are called up immediately after each refresh.
Sometimes the changes I make in these referenced macros have errors, and I'm
not sure if these errors can stop the running of the 72-hr macro.

Using now() at the start of each refresh is fine, but still means I have to
wait until the start of the next refresh to confirm the "refresh schedule" is
still working. Maybe the only way around this is to put another loop in the
refresh macro that shows now() more frequently than every hour . . .

I'm new to all this, and I just thought maybe there was a place or way to
see either a progress bar, or a list of running macros the way a list of
running programs can be seen (e.g., Windows Task Manager).


"DJ" wrote:

I'm working with a user-initiated macro (Excel 2003) that conducts a query
every hour for 72 hours. Is there a way to confirm whether or not this macro
is running without waiting to observe it conduct it's next scheduled query?

I really like this idea. I tested it and found that the status bar is updated
throughout the refresh (which takes a couple minutes). I tried some
variations and ended up with the following:

Application.StatusBar = €œProcessing Refresh€�
Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
Application.OnTime Now + TimeValue (€œ00:02:30€�), €œScreenUpdate€�

. . . the end of other referenced modules also include direction to
€œScreenUpdate€�, although a time delay wasnt needed in modules that dont use
RefreshAll.

Sub ScreenUpdate()
Application.ScreenUpdating = True
Application.StatusBar = €œAuto Refresh Schedule is Operating€�
End Sub

After completing the end of the 72 hour loop, I added
Application.ScreenUpdating = True
Application.StatusBar = False

Thanks for your help!


"Zone" wrote:

I'll barge in. You could use the status bar to show which macro is
in-process. Then, if one of the auxilliary macros is shown on the status
bar for an unusually long time, you'd know there's a problem. HTH, James

Sub Refresh ()
application.statusbar="Processing Refresh"
'more code
Call Macro1
application.statusbar="Processing Refresh (M1 complete)
Call Macro2
application.statusbar="Processing Refresh (M2 complete)
'more code
application.statusbar=false
End Sub

Sub Macro1()
application.statusbar="Processing Macro1"
'more code
End Sub

Sub Macro2()
application.statusbar="Processing Macro2"
'more code
End Sub

"DJ" wrote in message
...
Thanks JLatham!
I see how this lets me know when the last refresh was conducted. My
concern
has been that after initiating the 72-hr "refresh schedule" macro, I've
been
working on other macros that are called up immediately after each refresh.
Sometimes the changes I make in these referenced macros have errors, and
I'm
not sure if these errors can stop the running of the 72-hr macro.

Using now() at the start of each refresh is fine, but still means I have
to
wait until the start of the next refresh to confirm the "refresh schedule"
is
still working. Maybe the only way around this is to put another loop in
the
refresh macro that shows now() more frequently than every hour . . .

I'm new to all this, and I just thought maybe there was a place or way to
see either a progress bar, or a list of running macros the way a list of
running programs can be seen (e.g., Windows Task Manager).


"DJ" wrote:

I'm working with a user-initiated macro (Excel 2003) that conducts a
query
every hour for 72 hours. Is there a way to confirm whether or not this
macro
is running without waiting to observe it conduct it's next scheduled
query?

Great! Glad to help. James

"DJ" wrote in message
...
I really like this idea. I tested it and found that the status bar is
updated
throughout the refresh (which takes a couple minutes). I tried some
variations and ended up with the following:

Application.StatusBar = "Processing Refresh"
Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
Application.OnTime Now + TimeValue ("00:02:30"), "ScreenUpdate"

. . . the end of other referenced modules also include direction to
"ScreenUpdate", although a time delay wasn't needed in modules that don't
use
RefreshAll.

Sub ScreenUpdate()
Application.ScreenUpdating = True
Application.StatusBar = "Auto Refresh Schedule is Operating"
End Sub

After completing the end of the 72 hour loop, I added
Application.ScreenUpdating = True
Application.StatusBar = False

Thanks for your help!


"Zone" wrote:

I'll barge in. You could use the status bar to show which macro is
in-process. Then, if one of the auxilliary macros is shown on the status
bar for an unusually long time, you'd know there's a problem. HTH, James

Sub Refresh ()
application.statusbar="Processing Refresh"
'more code
Call Macro1
application.statusbar="Processing Refresh (M1 complete)
Call Macro2
application.statusbar="Processing Refresh (M2 complete)
'more code
application.statusbar=false
End Sub

Sub Macro1()
application.statusbar="Processing Macro1"
'more code
End Sub

Sub Macro2()
application.statusbar="Processing Macro2"
'more code
End Sub

"DJ" wrote in message
...
Thanks JLatham!
I see how this lets me know when the last refresh was conducted. My
concern
has been that after initiating the 72-hr "refresh schedule" macro, I've
been
working on other macros that are called up immediately after each
refresh.
Sometimes the changes I make in these referenced macros have errors,
and
I'm
not sure if these errors can stop the running of the 72-hr macro.

Using now() at the start of each refresh is fine, but still means I
have
to
wait until the start of the next refresh to confirm the "refresh
schedule"
is
still working. Maybe the only way around this is to put another loop
in
the
refresh macro that shows now() more frequently than every hour . . .

I'm new to all this, and I just thought maybe there was a place or way
to
see either a progress bar, or a list of running macros the way a list
of
running programs can be seen (e.g., Windows Task Manager).


"DJ" wrote:

I'm working with a user-initiated macro (Excel 2003) that conducts a
query
every hour for 72 hours. Is there a way to confirm whether or not
this
macro
is running without waiting to observe it conduct it's next scheduled
query?