[SOLVED] Save open workbook on a looped five minute timer infinitely

Save open workbook on a looped five minute timer infinitely

I'm trying to run an Excel macro that will save the open workbook, begin a hidden five minute timer, then loop back to the beginning nonstop.

It will save the workbook then get stuck in the timer loop only. It also locks up the workbook so you cannot interact with it anymore. I have to close it via taskkill.

The idea is to have the workbook open 24/7 on a PC where employees can scan a barcode to mark an item as "checked in/out". I have a script that backs up the workbook to a network drive, but most people will not remember to save it after scanning, so I want to automate that step.

Sub Macro3()
    ActiveWorkbook.Save
    MyTimer
End Sub

Sub MyTimer()
    Application.Wait Now + TimeValue("00:00:05")
    MsgBox ("Test seconds")
    Macro3
End Sub

Solution

Use of Application.OnTime would be better for your requirements ... see the MS Docs here Application.OnTime. It does not cause Excel to lock-up in the same way as Application.Wait. An example usage is the following ... this code must go in your ThisWorkbook Module ... it will not work in any other Module:

Option Explicit

Private dtScheduled As Date

Private Sub Workbook_Open()
    ScheduleSaving
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.OnTime dtScheduled, "ThisWorkbook.DoSave", Schedule:=False
End Sub

Private Sub ScheduleSaving()
    dtScheduled = Now + TimeSerial(0, 5, 0)
    Application.OnTime dtScheduled, "ThisWorkbook.DoSave"
End Sub

Sub DoSave()
    On Error Resume Next
    Debug.Print Now, "Saved"
    ThisWorkbook.Save
    ScheduleSaving
End Sub

To provide some explanation:

Workbook_Open() is an event handler that will run as soon as the Workbook is opened. It schedules the first save.

Workbook_BeforeClose() is another event handler that will run before the Workbook is closed. It cancels the next save (if there is one ... note the use of False for the Schedule parameter) ... without this, the Workbook would actually be re-opened in order to run the next save. It includes On Error Resume Next so that no error message is generated if there is no 'next save' scheduled.

ScheduleSaving(), obviously, schedules the next save event. If you want to test this code, you could change TimeSerial(0, 5, 0) to something like TimeSerial(0, 0, 5) so that the save is performed every 5 seconds instead of every 5 minutes.

DoSave() does the save ... I included a Debug.Print line so that you can see (in the VBE's Immediate window) when a save is performed ... if you want, you can delete this line when you are using the code 'for real'. It includes On Error Resume Next so that, if there is any problem with the save, a subsequent save is still scheduled.