Save Workbook, OnTime and BeforeClose Event

‘Following macro (SaveIt) will save an excel workbook at a particular time when opened. If you want to loop the macro so that VBA saves excel workbook, let’s say every 1 minute, then you should use Now + Timeserial(0,1,0), otherwise you can simply specify a time using Timeserial(12,0,0) which will save the macro at 12:00. You can put it in a module and call this macro in the Workbook.Open event in ThisWorkbook module in project explorer (VBA editor).

Dim MyTime as Date

Sub SaveIt()
ThisWorkbook.Save

MyTime = Now+TimeSerial(0,1,0)
Application.OnTime MyTime, “SaveIt”
End Sub

‘Following macro will end the ontime macro which otherwise will keep on running in background if the file is closed but excel instance remains open. You can put it in a module and call this macro in the Workbook.BeforeClose event in ThisWorkbook module in project explorer (VBA editor).
Sub EndSvMacro()
Application.OnTime MyTime, “SaveIt”, , False
End Sub

Comments are closed.