I have an Excel 2007 workbook that tracks production data by shift. At the
end of the shift the operator runs a macro that saves the workbook as a new
name (date, time, shift, etc.) and then it reopens a template file that
immediately saves the template as a working file to be used for the new
shift. I have trained the operators to save the file every ½ hour to
prevent data loss and have the auto back-up set for every 5 minutes.
Everything works great until there is a power loss or the working file is
inadvertently closed. When this happens all of the current shift data is
lost because when a new template file is opened it creates the working file
which in turn overwrites the data. Also, because a new template file is
created the recovery file is no longer available.
My thought is if the file could be saved or copied automatically via VB code
every 10 minutes as a different name and in a different location and I could
recover the data.
For example: VB code in working file.xlsm runs every 10 minutes and copies
itself to a file called working file b-up.xlsx located in c:\1 and then
returns to working file.xlsm to continue to be used until the end of the
shift.
Here is what I have€¦ the problem it does not return to the €œworking file€�.
Public RunWhen As Double
Public Const cRunIntervalSeconds = 120 ' two minutes
Public Const cRunWhat = "TheSub" ' the name of the procedure to run

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat, _
Schedule:=True
End Sub

Sub TheSub()
Application.DisplayAlerts = False

ActiveWorkbook.SaveCopyAs Filename:="Electronic Workstation-testing.xlsm"

ActiveWorkbook.Close


StartTimer ' Reschedule the procedure

End Sub

Any help would be appreciated.

Thanks