ansaurus

Question

how to execute a vba macro every 30 minutes automaticaly ?

Answer 1

+5 A:

Excel VBA DOM has a nice feature called Application.OnTime. This can schedule a macro to run at any time. To run a Macro every 15 min, just schedule for 15 min in the future on application startup, and then schedule 15 min in the future everytime the macro runs.

From this website: http://www.ozgrid.com/Excel/run-macro-on-time.htm

Example:

Private Sub Workbook_Open()
  Application.OnTime Now + TimeValue("00:15:00"), "MyMacro"
End Sub

Vincent McNabb 2010-07-25 21:27:52

thanks for the link.but how can i execute the macro without having to open the file? is it possible?

daria 2010-07-25 23:03:45

@daria update your question with the extra requirement :-)

Vincent McNabb 2010-07-25 23:12:45

And it's not possible to update a macro without opening a file because the macro is stored inside your file.Otherwise you will have to write your macro as a separate program, in which case you could use Windows scheduler to run it periodically.

Vincent McNabb 2010-07-25 23:14:02

@ McNabb it seems exactly what i need. how can i do this?

daria 2010-07-25 23:21:58

how can i write the macro in a separate program? i've never worked with vba so i've no idea about this

daria 2010-07-26 00:03:23

If you absolutely have to have the macro run from within Excel, you could write a simple batch file (Google ".bat batch files") to launch Excel and use Windows Scheduler to run the batch file. Put the code for your macro to run using the Launch/Open event. That way the code will be run when the work book is opened, the workbook is opened by a batch file, the batch file is run by Windows Scheduler.You could also look into VBScript or perhaps what you need to do can be done by a batch file.

Michael 2010-07-26 13:28:50

@Michael thanks so much it worked

daria 2010-07-26 14:38:59

ansaurus

tags:

views:

answers:

how to execute a vba macro every 30 minutes automaticaly ?

related questions