Running macros at a specific time

macsloth

New Member
Joined
May 13, 2003
Messages
6
Hi there,

I was wondering if there is a way I can get a macro to run at a specific time every day?

Would really appreciate any suggestions! Thanks!

Cheers,

Julian Wee
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

XLENT

Board Regular
Joined
Apr 30, 2003
Messages
100
look at ONTIME method in VBA help

or if you don't want the file open look into running a Workbook_Open event in conjuction with a windows scheduler to launch Excel.exe
 

Paul B

Well-known Member
Joined
Feb 15, 2002
Messages
569
Julian, welcome to the board, here is a short Marco that will pop up a message box at 6:00 PM

Code:
Private Sub Workbook_Open()
'Run YourSub at 6pm
Application.OnTime TimeValue("18:00:00"), "YourSub"
End Sub
Then in a normal module you'd have YourSub...

Code:
Sub YourSub()
MsgBox "Hello"
'incase the workbook is not closed, will set the timer for the next day
Application.OnTime TimeSerial(18,0,0), "YourSub"
End Sub
To put in this code, press alt and F11 to open the VBA editor, in the left hand window double click on thisworkbook, under your workbook name, and paste the workbok open code in the window that opens on the right hand side, then go to insert, module, and paste the code for "your sub" in the window that opens on the right hand side now this will run every time you open the workbook
 

macsloth

New Member
Joined
May 13, 2003
Messages
6
Thanks for the reply but I've tried searching for ONTIME and the search yielded nothing. A search for "ONTIME METHOD" yielded a lot of irrelevant entries.

Could you just tell me what I need to do please? Am using Excel 97.

Julian
 

XLENT

Board Regular
Joined
Apr 30, 2003
Messages
100
macsloth by name and nature.

we're not consultants.

ON TIME is XL's timing mechanism - you will need to ensure your file is open for this to work.

A windows scheduler will launch XL for you and open a specific file (ie. you don't need to keep it open) - if the file then has a workbook_open event in it, then it can do whatever it is you want done (run another macro or whatever). You will need to ensuer your application.displayalerts = false and then macro security is set to low.
 

macsloth

New Member
Joined
May 13, 2003
Messages
6
thanks.

didn't mean to sound like i was exploiting your help, it's just that i'm really new to this vba thing. in fact i've hardly used it. have no programming experience or training at all so all the syntax is essentially alien to me.

i need to harvest data from a reuters item that unfortunately doesn't have a time series so i need to build one.

i've searched quite a few websites but the closest i've got was running a macro on launching xl/wkbk.

i will be keeping the spreadsheet open so i thing ontime will be suitable. will try out the suggestions.

thanks again!
 

macsloth

New Member
Joined
May 13, 2003
Messages
6
hi paul,

sorry to be a bother but just like to confirm. i've opened a new module and just pasted:

Private Sub Workbook_Open()
'Run PHPNDF at 4.30pm
Application.OnTime TimeValue("16:30:00"), "PHPNDF"
End Sub


PHPNDF is the macro i want to run at 4.30pm. is that right?

thanks!
 

Paul B

Well-known Member
Joined
Feb 15, 2002
Messages
569
Julian, the Private Sub Workbook_Open() part needs to be put in the thisworkbook module if that is where you put it it should work, you will have to save and close and then open the workbook for it to run, here are instructions if I was not clear on it


To put in this code, press alt and F11 to open the VBA editor, in the left hand window double click on thisworkbook, under your workbook name, and paste the code in the window that opens on the right hand side, now this will run every time you open the workbook
 

macsloth

New Member
Joined
May 13, 2003
Messages
6
Hi Paul,

it works! thanks so much! just one more question, if i want to set it to run a few macros at that fixed time, i just put in 'Thisworkbook'

Private Sub Workbook_Open()
'Run PHPNDF at 4.30pm
Application.OnTime TimeValue("16:30:00"), "PHPNDF"
'Run TWDNDF at 4.30pm
Application.OnTime TimeValue("16:30:00"), "TWDNDF"
End Sub

with PHPNDF and TWDNDF being the 2 macros i need to run. Is that right?

julian
 

Paul B

Well-known Member
Joined
Feb 15, 2002
Messages
569
Julian, I have never tried to run two Marcos at the same time, I am not sure that will work, which one would try to run first? I think I would run the first one and then the second on a minute latter, or call the second sub from the first one like this

Code:
 Sub PHPNDF()
'First code here

'Name of the second code here
TWDNDF
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,089,766
Messages
5,410,296
Members
403,309
Latest member
chaithra

This Week's Hot Topics

Top