This macro saves a backup copy of the excel workbook within the same folder as the original and each time it runs it deletes the older backup.
Is there away to change the code so the backup is stored in different folder other than where the original is stored and still delete the older backup?
Lets say I want to store it on my C drive within a folder named Backup?
Is there away to change the code so the backup is stored in different folder other than where the original is stored and still delete the older backup?
Lets say I want to store it on my C drive within a folder named Backup?
VBA Code:
Sub SaveExit()
Dim fname As String, OldestFile As String, OldestDate As Date, Directory As String, CurrentName As String
CurrentName = ActiveWorkbook.Name
fname = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1) & " " & Format(Now, "dd-mmm-yy hh-mm-ss") & ".xlsm"
'fname = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1) & Format(Date, "dd-mmm-yy") & " " & Format(Time, "hh-mm-ss") & ".xlsm"
ActiveWorkbook.SaveAs Filename:=fname, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Directory = ActiveWorkbook.Path
If Right(Directory, 1) <> "\" Then Directory = Directory & "\"
fname = Dir(Directory & "*.xlsm", 0)
If fname <> "" Then
OldestFile = fname
OldestDate = FileDateTime(Directory & fname)
Do While fname <> ""
If FileDateTime(Directory & fname) < OldestDate Then
OldestFile = fname
OldestDate = FileDateTime(Directory & fname)
End If
fname = Dir
Loop
End If
ChDir ActiveWorkbook.Path
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=CurrentName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Kill OldestFile
'MsgBox OldestFile
Application.DisplayAlerts = True
Application.Quit
End Sub