How to loop a macro every 20 minutes
Hello,
I need assistance with some code that will run this macro every 20 minutes in an Excel workbook that is constantly open. I think it needs to loop somehow but it is beyond my programming skills. If anyone can point me to the right Method I would greatly appreciate it!!
Michael
Sub UpdateStats() Application.Run "Tracking.xls!BuyPROupdateHITS"
Application.Run "Tracking.xls!GetFE"
Sheets("STATS").Select
ActiveWorkbook.Save
End Sub
The other option is to send the process to sleep for 20 minutes using the Windows API.
http://mech.math.msu.su/~vfnik/WinApi/s/sleep.html
Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
Sleep 2000 ' 2000 milliseconds = 2 seconds to delay
It might not be the solution your looking for it reallt depends on what you have in mind.
Hello Michael,
Play around with the following and you should be able to get the result you need.
ChasAA
[Code starts]
Sub start()
' Put this in your workbook open event
Application.OnTime Now + TimeValue("00:00:15"), "runUpdate"
' I have set it for 15 seconds in this example change to "00:20:00" for 20 minutes
End Sub
Sub runupdate()
' this is where your code for the updating will go
' the following two lines are only so that I could see what was happening
Selection.Value = "Last ran at " & Format(Now, "hh:mm:ss")
Selection.Offset(1, 0).Select
Call start ' make sure you add this line to start the timer again
End Sub
[Code ends]
