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???

 

[hgbegdhegdh]

Actually it does not, since there could be just one "ActiveWorkbook" at a time.
In this case btw, it wouldn't matter if "ThisWorkbook" or "ActiveWorkbook" was used because of the fact, the OnTime method temporarily activates the workbook containing the scheduled macro, launches it, and sets Excel back to its prior state afterwards. Refering to the above code, the active workbook would always be the same as the "ThisWorkbook".
As observed by other forum members, the scheduled code is sometimes executed more often than intended. This is because the scheduled code has to be canceled, preferably at the time the workbook containing the scheduled code is being closed. Otherwise Excel will keep opening the workbook with the scheduled code, will run the code, will close that workbook afterwards, until Excel is completely closed. In order to make canceling possible, the scheduled time has to be stored to be able to let Excel know, which scheduled time has to be cancelled.
Would not recommend you saving all open workbooks using the OnTime method (at such a short interval).

Thank you GWteB for the prompt reply.