Automating a Report with Application on Time

[kv2singh]

Hey Guys,

I am pretty new to VBA and have never really had any training with coding for it. I am setting up an automated report but am having trouble getting the application on time event to run more than 24 hours. The on time functions stop after 24 hours, how would I get this to work past 24 hours.

The Reportupdate Sub updates quite a few cells based on the current, that why it is run so often. I am sending the report out at 6 am everyday and the report covers a 24 hour interval from midnight to midnight, hence the last report update time before 6 am being 0:00:00.

Here is my Code:

Please Login or Register  to view this content.

Help on getting the application on time timers to work past 24 hours would be appreciated.

[Kaper]

As for the time, you probably(?) can use for midnight next day and 6:00am next day:

Please Login or Register  to view this content.

You may also try:

Please Login or Register  to view this content.

[kv2singh]

Ill try the first method. Just to clarify this will allow my application on time function to run past 24 hours correct.

[kv2singh]

With the first method whenever i open the excel file from a closed position it updates the values and sends out a report even though the specified time has not been reached.

Application.OnTime 1+TimeValue("00:00:00"), "Reportupdate"
Application.OnTime 1+TimeValue("06:00:00"), "sendReminderMail"

[Kaper]

A comment on codes from post #2. They rely on a way date and time are stored in excel. Days are counted from "calendar start" date and time during day is a fraction of day. So 6:00 is 1/4, 8:00 is q/3 etc. If we count from midnight tonight, 6:00 was just y hours or 6/24 of the day. Tomorrow morning 6:00 will be on 1day6hrs from todays midnight, or in other words 30hrs from this moment.