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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

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

ADVERTISEMENT

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

ADVERTISEMENT

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

Forum statistics

Threads
1,144,418
Messages
5,724,202
Members
422,542
Latest member
steve011

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top