Application.OnTIme

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

hi,

I created a copy macro within Excel that I need done at a
certain time each day. The copy macro (called copy3) is
under the Modules portion in Visual basic.

I put the following in the Sheet2 page in Visual basic:

Private Sub Timer()

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

End Sub


And it does not run the macro at 13:00.

What have I done wrong?


thanks,
Mike
 
Mike,

You need to call the procedure before it will do anything. Ideally, it
should be a in a module so that it can be called from different locations.

All in all, if you do not run the routine you posted, it will not work.


Robert
 
Stupid question:

How do you run the timer?


Ideally I would like this to run automatically. That is
I would use Scheduled Tasks to bring the worksheet up
then later in the day have the copy macro run so I don't
want to have to run the timer unless this can be done
automatically when the spreadsheet comes up.


thanks
 
You can do that too. But you need to add a call on the Workbook_Open event in
order to call the procedure. Then you can use another onTime to close the
workbook after a few seconds.

Have a look at ExcelTip for the code on how to unload the workbook after the
time has elapsed.

Robert
 
You could place the code in the ThisWorkbook module as a Workbook_Open Sub
which would run when the workbook is opened.

This would start the Timer, which would run copy3 at 13:00:00

If you are going to use Task Scheduler to open the workbook at a certain
time(13:00:00) each day, you wouldn't need the Application.OnTime code.

Just have the copy3 code in the Workbook_Open code. TS would open the
workbook, copy3 would run, more code would save and close the workbook if
desired.

Gord Dibben Excel MVP
 
I am having a similar problem. I'm attempting to get an excel file t
open up and auto open a macro. I am using Windows 2000 server wit
Scheduled Tasks. I set up a task with a batch file that looks lik
this:

c:\progra~1\micros~3\Office\Excel.exe D:\macro\test.xls

When I tell the task to run, it shows a status of running, howeve
never does anything. I don't see Excel open anywhere in the activ
processes. In fact, the process never stops running and I have t
manually tell it to end task.

I've tested this particular line in the Run prompt and the macro open
up and runs perfectly.

Any ideas why this isn't working
 
Back
Top