Application.OnTime query

[sarahmichelle]

Hi All

I have been researching how to resolve my Application.OnTime issues with regards to my VBA code but have not had any luck. My code does not execute at all.

I am not experienced with VBA, but simply want my workbook to save to a drive 4 times a day. Thus, I have the below code repeated 4 times in VBA for each time I wish to save my file & with different file names. Is there any alternative to Application.OnTime that I can use to carry out this purpose?

Sub SaveWorkbookAM()
Application.OnTime TimeValue("01:00:00"), "AMmacro"
End Sub

Sub AMmacro()
Dim MyFilePath As String
Dim MyFileDate As String
Dim MyFileName As String
MyFilePath = "Y:\Tissue Furnish Tracker\"
MyFileDate = Format(Date, "mm-dd-yyyy")
MyFileName = MyFilePath & MyFileDate & "_Dayshift.xlsm"
ActiveWorkbook.SaveCopyAs Filename:=MyFileName
End Sub

Please help. Thanks.

 

[Dan_W]

Hi - so the code above has two subroutines. The first subroutine (SaveWorkbookAM) appears to call the second subroutine (AMmacro) at 1am. So when you say you have the code repeated 4 times a day, do you mean that someone manually runs the first subroutine 4 times throughout the day? Do they change the target time each time they run the sub?

Also, Application.OnTime requires that Excel be running at that designated time. Is Excel running at all required times that you want the workbook saved? If not, then there is something called Task Scheduler that is built into Windows that you could conceivably use for this, but I just thought I would try and understand your needs a little better first.

 

[johnnyL]

Also, Application.OnTime requires that Excel be running at that designated time. Is Excel running at all required times that you want the workbook saved? If not, then there is something called Task Scheduler that is built into Windows that you could conceivably use for this, but I just thought I would try and understand your needs a little better first.

Just to clarify what some might misunderstand there. The actual workbook that has the scheduling doesn't have to remain open, but the Excel application itself does have to remain open, otherwise Task Scheduler or something would have to be utilized.

I now return you to your regular scheduled programming.

 

[johnnyL]

I need to add to my last post, if the workbook is closed that you have an event scheduled in, if that workbook is not in a trusted location, you may find that macros in the workbook are disabled when it is opened, and the scheduled macro will not run.

 

[johnnyL]

@sarahmichelle it would help us if you answered a couple questions.

1) Do you want this code to be ran when workbook is opened, or just at set times of the day?
2) What times of the day are you wanting this schedule to run? You mentioned 4 times. Is that when workbook is initially opened then 3 more times?
3) Is the same code going to be run each time?