Autosave Every 5 minutes.......

[Guitarfool5931]

I'm trying to write a macro in excel that will save the document every couple of minutes. After searching the forums here for a bit I found something that might work:

Sub test()
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 30
waittime = TimeSerial(newHour, newMinute, newSecond)

Do
ActiveWorkbook.Save
Loop
End Sub

The only thing about this is that it runs constantly and won't stop saving. Is there a way to do this where it will only save every 5 minutes or so???

 

[Oaktree]

Have a look at the OnTime method in vba help.

 

[Guitarfool5931]

Thanks! Found something I might be able to use there. But not sure how to write the code in the macro??:


<CODE>Sub test()</CODE></PRE><CODE>

Do
Application.OnTime Now + TimeValue("00:05:00"), "ActiveWorkbook.Save"</PRE>
Loop</PRE>
End Sub</PRE>
Sorry, I'm a newbie at macros.


</PRE></CODE>

 

[Oaktree]

Put this code in the ThisWorkbook module

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:05:00"), "SaveThis"
End Sub

And, put this code in a standard module (e.g. Module1)

Sub SaveThis()
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True

Application.OnTime Now + TimeValue("00:05:00"), "SaveThis"
End Sub

 

[Guitarfool5931]

Do I have to run this code everytime I open up the workbook? Not sure if I put this in right but it seems to work when I run it.

 

[Oaktree]

By putting the Workbook_Open event in the ThisWorkbook module, it will automatically trigger the first save 5 minutes after the workbook is opened (and all subsequent saves).

 

[Guitarfool5931]

Yup it works. Great stuff. Everyone on these forums has been a great help. Thanks, I really appreciate it again!

 

[Guitarfool5931]

So this was running fine til today it started saving about every 2 minutes instead of five. I haven't changed anything but here is what the code looks like. Might have entered it wrong.


Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:05:00"), "SaveThis"
End Sub
____________________________________________________________
Sub SaveThis()
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
Application.OnTime Now + TimeValue("00:05:00"), "SaveThis"
End Sub

 

[Oaktree]

That looks OK... Is it possible that some of the saves are autorecover saves? Check the autorecover settings at tools --> options --> save.

 

[SQChevy]

Time is speeding up for you...Test the counter using a pop up window also to make sure it's your code. The other possibility is that Excel's autosave function is also saving and if the file was offset from Excel's autosave by about 2 min then it would seem like it saved every 2 minutes. Instead of 5 for each autosave.