D
Deleted member 6694
Guest
As I don't trust Excel own autosave feature as it's not 100% fail safe, I'm trying to create some VB code to autosave the workbook every 5 minutes to a backup folder and timestamp the filename. I can autosave it no problem by itself using some simple code and I can also save it as a timestamp no problem using some other VB code but not both together.
I got the codes below that I use so maybe they can be merged together or something completely different created. It's going to be simple but my limit of VB is just hacking, merging, editing other VB code rather than creating it fresh.
AUTOSAVE - http://www.ozgrid.com/forum/showthread.php?t=126722&p=484912#post484912
VB:
In ThisWorkbook Module;
VB:
TIMESTAMP CODE - https://excelribbon.tips.net/T013195_Creating_a_Dated_Backup_File.html
This is one way I've tried when I merged the 2 together but it's not saving anything so hoping someone here can correct it rather than hoying this question on an excel forum.
VB:
In ThisWorkbook Module;
VB:
I got the codes below that I use so maybe they can be merged together or something completely different created. It's going to be simple but my limit of VB is just hacking, merging, editing other VB code rather than creating it fresh.
AUTOSAVE - http://www.ozgrid.com/forum/showthread.php?t=126722&p=484912#post484912
VB:
Code:
Public dTime As Date
Sub AutoSaveAs()
dTime = Time + TimeValue("00:05:00")
With Application
.OnTime dTime, "AutoSaveAs"
.EnableEvents = False
.DisplayAlerts = False
ThisWorkbook.SaveAs "FilePath&NameHere(no .xls)"
.EnableEvents = True
End With
End Sub
In ThisWorkbook Module;
VB:
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "AutoSaveAs", , False
End Sub
Private Sub Workbook_Open()
dTime = Time + TimeValue("00:05:00")
Application.OnTime dTime, "AutoSaveAs"
End Sub
TIMESTAMP CODE - https://excelribbon.tips.net/T013195_Creating_a_Dated_Backup_File.html
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sFileName As String
Dim sDateTime As String
With ThisWorkbook
sDateTime = " (" & Format(Now, "yyyy-mm-dd hhmm") & ").xlsm"
sFileName = Application.WorksheetFunction.Substitute _
(.FullName, ".xlsm", sDateTime)
.SaveCopyAs sFilename
End With
End Sub
This is one way I've tried when I merged the 2 together but it's not saving anything so hoping someone here can correct it rather than hoying this question on an excel forum.
VB:
Code:
Public dTime As Date
Sub AutoSaveAs()
dTime = Time + TimeValue("00:05:00")
Dim sFileName As String
Dim sDateTime As String
With ThisWorkbook
.OnTime dTime, "AutoSaveAs"
sDateTime = " (" & Format(Now, "yyyy-mm-dd hhmm") & ").xlsm"
sFileName = Application.WorksheetFunction.Substitute _
(.FullName, ".xlsm", sDateTime)
.SaveCopyAs sFilename
End Sub
In ThisWorkbook Module;
VB:
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "AutoSaveAs", , False
End Sub
Private Sub Workbook_Open()
dTime = Time + TimeValue("00:05:00")
Application.OnTime dTime, "AutoSaveAs"
End Sub