auto backup with code

[rjmdc]

hi
i have auto backup when closing file
excel saves auto 10 minutes however if the book corrupts all code is lost
how can i program my backup module to run every hour?

 

[Saurabhj]

Hi,

You can create backup of excel file after every one hour.

In workbook events:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnTime Now() + TimeValue("01:00:00"), "callTimer", False
End Sub

Private Sub Workbook_Open()
    callTimer
End Sub

In module:

Sub callTimer()
    Application.OnTime Now() + TimeValue("01:00:00"), "COPYBACKUP"
End Sub
'It will create backup of file in the same folder with filename+currentdatetime.
Sub COPYBACKUP()
       ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5) & Format(Now(), "DDMMYYHHMM") & ".xlsm"
       callTimer
End Sub

Thanks
Saurabh

 

[rjmdc]

how does this work?
this is my backup code thill now
where do i add this?

Sub Auto_Save()
Dim saveDate As Date
Dim saveTime As Variant
Dim formatTime As String
Dim formatDate As String
Dim backupFolder As String
Dim FileExt As String
Dim MsgBoxResult As String
Dim ThisFileName As String


MsgBoxResult = MsgBox("Would you like a backup of this file?", vbInformation + vbYesNo)
If MsgBoxResult = vbYes Then

saveDate = Now
FileExt = ".xlsm"

formatDate = Format(saveDate, "MM-DD-YYYY hh.MM")


Application.DisplayAlerts = False
backupFolder = "M:\all\my name\_0 Document Dates\Backups\Log\"

ThisFileName = "Log " & formatDate & FileExt

ActiveWorkbook.SaveCopyAs FileName:=backupFolder & ThisFileName
Application.DisplayAlerts = True

MsgBox "Backup Successfully In The Path " & backupFolder

End If
End Sub

 

[mohadin]

Change

Sub callTimer()
    Application.OnTime Now() + TimeValue("01:00:00"), "COPYBACKUP"
End Sub

to

Sub callTimer()
    Application.OnTime Now() + TimeValue("01:00:00"), "Auto_Save"
End Sub

And your code in a Module
And
In workbook events:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime Now() + TimeValue("01:00:00"), "callTimer", False
End Sub
Private Sub Workbook_Open()
callTimer
End Sub

Forget about

Sub COPYBACKUP()
       ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5) & Format(Now(), "DDMMYYHHMM") & ".xlsm"
       callTimer
End Sub

 

[rjmdc]

if i understand, remove mine from workbook events and put into a module the add yours to workbook events?

 

[mohadin]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime Now() + TimeValue("01:00:00"), "callTimer", False
End Sub
Private Sub Workbook_Open()
callTimer
End Sub

In workbook events

And the rest you code and callTimer in a module


BTW It's not my code but

Saurabhj​