Auto Save excel workbook every 5 minutes

[RINCONPAUL]

I have AutoRecover set for 5 mins, an excel workbook with macros on board, open for about 8 hours recording data ends up producing a "out of memory consider excel 64bit" message. Thinking that AutoRecover will have saved a copy was a disappointment as for whatever reason it hadn't!

OK I thought I would add some code to auto save, and found some by SamT back in 2013. However it doesn't like the line "Application.OnTime......False". Any suggestions?

Private Sub Workbook_Open()
ThisWorkbook.CustomDocumentProperties.Add "shed1", False, 3, Now
schedule
End Sub

Sub schedule()
ThisWorkbook.CustomDocumentProperties("shed1").Value = DateAdd("n", 5, Now)
Application.OnTime ThisWorkbook.CustomDocumentProperties("shed1").Value, "schedule"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime ThisWorkbook.CustomDocumentProperties("shed1").Value, "schedule", , False
End Sub

[SamT]

First, Rename Sub "schedule" to anything else, ie "ScheduleX, or "Schedule_A_Save." I for one, saw "schedule" and thought Schedule & [Requires :=True or False]


Then

Sub Schedule_A_Save()
ThisWorkbook.CustomDocumentProperties("shed1").Value = DateAdd("n", 5, Now)
Application.OnTime EarliestTime:=TimeValue(ThisWorkbook.CustomDocumentProperties("shed1").Value), _
Procedure:="Schedule_A_Save",
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime EarliestTime:=TimeValue(ThisWorkbook.CustomDocumentProperties("shed1").Value), _
Procedure:="Schedule_A_Save", Schedule:=False
End Sub

[RINCONPAUL]

Thanks SamT.
There was a "," in your code after Save" that I removed to get rid of "red" text. Now I'm getting "Method 'OnTime' of object_Application failed"
Last two lines highlighted in "yellow"?

[Logit]

.
Try this :



Dim TimeToRun


'Sub auto_open()
' Call ScheduleclrCol 'uncomment this for code to auto run when sheet is opened
'End Sub


Sub ScheduleclrCol()
TimeToRun = Now + TimeValue("00:05:00") '<----- adjust time to fire here HH:MM:SS
Application.OnTime TimeToRun, "clrCol"
End Sub


Sub clrCol()
Calculate

'Place your code here to save workbook. Could be a 'call' the SAVE MACRO

Call ScheduleclrCol
End Sub


'Sub auto_close() 'uncomment this for code to auto stop when sheet is closed
' On Error Resume Next
' Application.OnTime TimeToRun, "clrCol, , False"
'End Sub


Sub manual_stop() 'button click for stop (Manual Stop)
On Error Resume Next
Application.OnTime TimeToRun, "clrCol, , False"
End Sub

[RINCONPAUL]

Doesn't appear to be working Logit. At the same time it's not throwing up anything that needs Debugging, so that's a positive! I placed that code in 'This Workbook' and Macro2 in a Module. Should Sub ScheduleclrCol go in a module too?

[snb]

See: http://www.snb-vba.eu/VBA_Application.OnTime_en.html

[Logit]

.
Let's simplify it to the very basics :



Option Explicit


Sub TimrRun()
MsgBox "Hi !", vbOKOnly, "What ?" '<---- replace this line with a call to your other macro or the macro code itself
Application.OnTime Now + TimeValue("00:00:05"), "TimrRun" '<-- presently set to run every 5 secs. Edit as required
End Sub





Paste the above into a Routine Module. Start Timer with a Command Button on the worksheet attached to the macro.

[RINCONPAUL]

Logit, Yes that works fine. Simple is best!
Thanks heaps :clap:

[Logit]

Good show