ontime method?

[max]

I need to copy the value in cell A1 to cell C1 at 700am each day. Can
Excel 2003 automate this process? Would the "ontime method" be the
correct approach to take? Thanks.

 

[Dave Peterson]

It might be.

If you already have excel open and and have run a macro that told excel to do
the copy at 7:00AM, then yes.

But if excel is closed or you haven't started that onTime macro, then probably
no.

An alternative is to use a Windows Scheduler program (many newer versions of
Windows come with a scheduler). And you could visit www.shareware.com to find
one if you don't have one.

Then you could schedule a program (or a workbook) that starts at 7:00AM. This
workbook could have an Auto_open() procedure that copies the cell. (You do have
to have the pc running.)

If you want to read more about Excel's VBA OnTime method, visit Chip Pearson's
site:
http://www.cpearson.com/excel/ontime.htm

 

[max]

I created a macro that looks something like this:


Sub Macro7()

Application.OnTime TimeValue("07:00:00"), "Macro4"

End Sub


It works when I start it manually. Where would I insert the lines
relating to the Starttimer function so I can have the macro start
automatically each day? I looked at the related info on the Pearson
site but don't see how I would apply it to my situation. This is my
first experience with Excel macros and VBA.

 

[Dave Peterson]

If you open the workbook before 7:00 AM each day (your responsibility if you
don't use a windows scheduling program), you can just name your macro7:
Auto_open()

Sub Auto_Open()
Application.OnTime TimeValue("07:00:00"), "Macro4"
end sub

Auto_open will run each time you open the workbook. Then xl will wait for 7:00
AM to run Macro4.

 

[max]

Auto_open worked for me but what if I wanted to automate more than one
macro? Only one macro can be named Auto_open, right? Are there
variations on Auto_open that can be used with multiple macros?

 

[Dave Peterson]

You could use workbook_open, but I think the real answer is to call those other
macros from your Auto_open/workbook_open procedure:

sub auto_open()
call macro1
call macro2
call macro3
end sub

 

[max]

Actually, that's what I"ve done so I'll see what happens tomorrow
morning. I need 7 different macros to run at the top of the hour 700
through 1300 so I entered 7 lines for each time and macro:

Application.OnTime TimeValue("07:00:00"), "Macro4"

I guess that's what you meant with your last response. Thanks for
taking the time to help. It would have taken me weeks to figure it out
on my own if at all.

 

[Dave Peterson]

Actually, I thought you wanted all 7 macros to run at 7:00.

But you could try a small test:

Option Explicit
Sub testme()
Application.OnTime Now + TimeSerial(0, 0, 10), "aaa1"
Application.OnTime Now + TimeSerial(0, 0, 14), "aaa2"
Application.OnTime Now + TimeSerial(0, 0, 16), "aaa3"
Application.OnTime Now + TimeSerial(0, 0, 17), "aaa4"
End Sub
Sub aaa1()
MsgBox "aaa1"
End Sub
Sub aaa2()
MsgBox "aaa2"
End Sub
Sub aaa3()
MsgBox "aaa3"
End Sub
Sub aaa4()
MsgBox "aaa5"
End Sub

to see if it does what you expect.

 

[max]

This is what I ended up with and it worked perfectly:

Sub Auto_Open()
'
' Auto_Open Macro
' Macro recorded 2/7/2005 by maxoblivion
'
Application.OnTime TimeValue("07:00:00"), "Macro1"

Application.OnTime TimeValue("08:00:00"), "Macro2"

Application.OnTime TimeValue("09:00:00"), "Macro3"

Application.OnTime TimeValue("10:00:00"), "Macro4"

Application.OnTime TimeValue("11:00:00"), "Macro5"

Application.OnTime TimeValue("12:00:00"), "Macro6"

Application.OnTime TimeValue("13:00:00"), "Macro7"

End Sub

Thanks again for your help.