Hi, I use this forum a lot for learning purposes, and this is the first question that I've had that I can not find the answer to.
Here is my code:
Function CheckAndBackup()
End Function
The code works great, the only problem is that the "Private Sub Workbook_Open()" of the new file calls a userform and interrupts the closing of the previous file. Is there any way that I can successfully close the previous file and keep this "Private Sub Workbook_Open()" as is?
And feel free to use this code, it is a generic function that works great for automatically backing up excel files.
Here is my code:
'Check last backup date
Dim LastBackupDate As Date
Dim DateDifferenceCheck As Integer
LastBackupDate = Sheets("calculations").Range("w22").Value
DateDifferenceCheck = Now() - LastBackupDate
If DateDifferenceCheck > 10 Then 'backup is required
MsgBox DateDifferenceCheck & " Days have passed since the last backup (" & LastBackupDate & "). Backup will now proceed and the program will restart."
'update last backup date for use in all save files
Sheets("calculations").Range("w22").Value = Now()
'save current file
Application.DisplayAlerts = False
ThisWorkbook.Save
'save backup version of the file
Dim BackupFolder As String
Dim BackupFileName As String
Dim FullFileName As String
Dim Original_File As String
Original_File = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
BackupFolder = "\\ServerName\BackupLocation"
BackupFileName = "Backup - " & Day(Now()) & "." & Month(Now()) & "." & Year(Now()) & " - " & Hour(Now()) & "h" & Minute(Now())
FullFileName = BackupFolder & "\" & BackupFileName & ".xlsm"
ActiveWorkbook.SaveAs FileName:=FullFileName, FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False 'after this save we are working from the backed up file so:
'Continue working from original version
Workbooks.Open Original_File
ThisWorkbook.Close
Application.DisplayAlerts = True
End If
The code works great, the only problem is that the "Private Sub Workbook_Open()" of the new file calls a userform and interrupts the closing of the previous file. Is there any way that I can successfully close the previous file and keep this "Private Sub Workbook_Open()" as is?
And feel free to use this code, it is a generic function that works great for automatically backing up excel files.
Last edited: