Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 27th, 2009, 01:22 PM
Authorized User
 
Join Date: Jan 2009
Posts: 20
Thanks: 2
Thanked 0 Times in 0 Posts
Default Schedule Excel to run a macro at a specific time and date

I know how to schedule a macro to run at a specific time but is there a way to schedule to run at a specific time on a specific date? I have something that I need to occur on the 1st of each month at 5:00 AM. Not sure how to accomplish this.
 
Old January 29th, 2009, 06:09 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

There is no direct way in VBA to schedule a Macro for a specific date. You can use Application.OnTime to trigger a macro at specifed time.

You can either create an Exe and add it to Windows scheduler or Check for date in your macro and call the macro by resetting Application.OnTime



Code:
Sub MyProc()
If Day(Now) = 1 Then
    
    ' Execute the proc
End If
' extend procedure schedule by a day
 Application.OnTime Now() + TimeValue("24:00:00"), "MyProc"
End Sub
Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old January 29th, 2009, 01:49 PM
Authorized User
 
Join Date: Jan 2009
Posts: 20
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Thanks for the help Shasur.
 
Old February 5th, 2009, 10:31 PM
JP JP is offline
Authorized User
 
Join Date: Apr 2008
Posts: 57
Thanks: 1
Thanked 3 Times in 3 Posts
Default

You could use the technique found here:

Running Excel on Windows Task Scheduler

Or set up Task Scheduler to open a specific workbook at that time. Put your macro code in the workbook's Workbook_Open() event.

Or if you have Outlook running, you can adapt the code found here to automatically open and run an Excel macro:

Handling Multiple Inboxes

For example, if you set up a task reminder in Outlook to trigger at the time you want, you can set up an event handler in Outlook to open the Excel workbook and run the appropriate macro at that time.

HTH

Quote:
Originally Posted by SheriV View Post
I know how to schedule a macro to run at a specific time but is there a way to schedule to run at a specific time on a specific date? I have something that I need to occur on the 1st of each month at 5:00 AM. Not sure how to accomplish this.
The Following User Says Thank You to JP For This Useful Post:
SheriV (March 18th, 2009)
 
Old March 18th, 2009, 04:12 PM
Authorized User
 
Join Date: Jan 2009
Posts: 20
Thanks: 2
Thanked 0 Times in 0 Posts
Default Thanks

Thanks for the info JP.






Similar Threads
Thread Thread Starter Forum Replies Last Post
Schedule a VBA macro to run at a specific time marshall04b Excel VBA 8 October 31st, 2015 08:41 AM
Schedule a VBA macro to run at a specific time peterlihh Other Programming Languages 0 October 24th, 2008 07:15 PM
run a macro at a certain date s_gh Excel VBA 4 May 17th, 2006 06:34 AM
Excel Formula End Range Changes when Macro Is Run maaron Beginning VB 6 0 October 11th, 2005 10:33 AM
Run macro when exit/close excel dgilford Excel VBA 1 September 8th, 2005 02:42 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.