Running macros at a specific time

[macsloth]

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

 

[XLENT]

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]

Julian, welcome to the board, here is a short Marco that will pop up a message box at 6:00 PM

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...

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]

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]

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]

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]

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]

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]

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]

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

 Sub PHPNDF()
'First code here

'Name of the second code here
TWDNDF
End Sub