I would like to automatize scheduled report on my company. I found out a method called Application OnTime in Excel, which after some reading looks like a perfect match.

The task is following, on chosen periods during day I would like to import data through powerquery and then save a file. I would like to work it all the time. My simple code looks like that:

Private Sub Workbook_Open() Application.OnTime TimeValue("06:30:00"), "Module1.MyMacro" Application.OnTime TimeValue("09:30:00"), "Module1.MyMacro" Application.OnTime TimeValue("12:00:00"), "Module1.MyMacro" Application.OnTime TimeValue("14:29:00"), "Module1.MyMacro" End Sub Sub MyMacro() ActiveWorkbook.RefreshAll Application.Wait (Now + TimeValue("0:05:00")) ActiveWorkbook.Save End Sub

Due to overlong refresh of the data I gave extra 5 mins before saving file. However I encountered problem, after some time my script stops working. I have tried different solutions from the Internet, unfortunately I couldn't complete the task on my own.

I have a request If someone could tell me abaout what I've forgotten and explain the issue.

9

1 Answer

The first parameter is EarliestTime. Your post lack some detail, but I guess it'll start a bit late. The third parameter is LatestTime. Try something like this:

Application.OnTime TimeValue("06:30:00"), "Module1.MyMacro", TimeValue("06:40:00")

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password

Post as a guest

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.