Application OnTime

Z

Zoot

Board Regular
Joined
Sep 18, 2003
Messages
123
I need a Macro to run automatically every night at midnight.

At the moment my code is

Sub AutoUpdate()

Application.OnTime TimeValue("00:00:00"), "ThisWorkbook.FindAllAddresses"

End Sub

However I need to actually run this code to get it to execute at the set time. How do I alter it to automatically run every night?

Zoot
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Zoot,

In order to execute your OnTime Code you workBook needs to remain open. What I think you want to do is to create a macro that runs automatically when you open the workbook and then schedule the workbook to open every night at Midnight using the Windows Scheduler found in the Control Panel?
 
Upvote 0
You could call the macro from the Workbook_Open event. That way it is always running.

EDIT: I like Bozacke's idea of using the Scheduler to open the workbook. Look at the Help for OnTime -- you can specify a time window (like, 60 seconds) during which the CollectAddresses code will run if the workbook is ready.

Denis
 
Upvote 0
Thanks for your help guys - that works very well using the scheduler and Workbook_Open.

To take it a stage further, however. I may have users creating a number of copies of this workbook and they may not be up to (or remember to) schedule the open workbook for each workbook.

Any way to do this by code?

Zoot
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question
How to stop a procedure with Application.Ontime
Replies
2
Views
258
oldfisher
O
J
  • Question
VBA code to send an email if a certain IF Criteria is met that loops when the spreadsheet is open
Replies
1
Views
239
joeyc123
J
R
  • Question
Real Time Clock
Replies
6
Views
290
rakupareek
R
R
  • Question
Issue at time change (spring forward) in excel vba.
Replies
0
Views
92
Russel R
R
corentint
  • Solved
A recursive procedure
Replies
3
Views
126
John_w
J

Forum statistics

Threads
1,214,998
Messages
6,122,643
Members
449,093
Latest member
Ahmad123098

Share this page

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
Back
Top