Have a Macro run farther than 24 hours in the future...

Posted on 2005-03-01
Medium Priority
Last Modified: 2010-05-02
Within Excel XP and VBA, is there a way, using Application.OnTime or some other method, to schedule a macro to execute once at a period more than 24 hours in the future?

I'm trying to avoid using the Windows Task Schedule because it has a habit of crashing  Excel for some reason.
Question by:dhienzsch
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 35

Accepted Solution

[ fanpages ] earned 1000 total points
ID: 13435289

In the routine you had identified as your "OnTime" event, check the current system date (using "Now()") to see if the date you wish the code to run has been reached.  If the date is still in the future, reschedule the "OnTime" event.  24 hours later the routine will trigger again, and eventually the date required will match the system date.



Author Comment

ID: 13460487
I haven't forgotten about this question... I just haven't had time to test yet.

LVL 35

Expert Comment

by:[ fanpages ]
ID: 13460990

That's OK... no rush... we've still got time ;)

Author Comment

ID: 13657723
Wow... completely spaced providing a response.  

The suggestion worked.  I use a routine where I use NOW to check the system day and if it is anything other than the day I want, I make it sleep for 24 hours from the current time - 15 minutes (so it doesn't cause itself to run continuously).  

Note, this won't work if Macro security level is set to high.
LVL 35

Expert Comment

by:[ fanpages ]
ID: 13666366
Glad we got there in the end.

Thanks for the points/grading.


[ http://NigelLee.info ]

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month11 days, 11 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question