PDA

View Full Version : [SOLVED] AutoBackup / Make 1 extra copy of my workbook



DarinM
05-28-2015, 05:48 AM
Hi guys,

I tried googling a solution to my problem, and it hasn't seemed to work.

I have just combined all my tracking excel's for my various projects into 1 workbook, now I am afraid because all my eggs are in 1 basket, so I would like to create a backup everytime I save, but only 1 or 2 backups, not a new one each time I save.

I found a macro to do that, but it saves another copy each time, 100 saves = 100 excels.

I did already do the Save as> tools > make a backup, as well as the excel options > save > location of drive, saved as a macro-enabled workbook, however I see no files in either of those locations. I have the interval to 1minute to test, and see no temp files or anything.

Hope you can help!

thanks,

Darin

Yongle
05-28-2015, 08:11 AM
I have just combined all my tracking excel's for my various projects into 1 workbook, now I am afraid because all my eggs are in 1 basket, so I would like to create a backup everytime I save, but only 1 or 2 backups, not a new one each time I save.

I am confused :confused:
Do you want a new back-up when you save and exit?
Do you not want a new back-up when you save and exit? Perhaps every 5th time you exit
Do you want back-ups when you save but do not exit, but not every time? Perhaps every 5th time you save?
Or is it something else?

DarinM
05-28-2015, 08:22 AM
Sorry for the confusion!

When I hit Save, I want it to save my current document, and make a backup called "DocumentName-Backup".
everytime I hit save thereafter, I want it to overwrite DocumentName-Backup, instead of being DocumentName-Backup1,2,3,4,5,6 X as many times as I save.

hope that helps.

I won't be exiting this excel often, so backup upon save only would be best.

thanks.

SamT
05-28-2015, 08:40 AM
This is what I use in the ThisWorkbook code module of MyPersonal.xls. You will have to edit it to suit your needs. The time stamp keeps all the backups in date/time order, ("42148.7571_Personal.xls.Bak".) Usin 4 digits of time information lets me backup every second, and the Bak extension keeps me from accidentally opening a file. As of Sep 2014, I only have 11 backups.

Note that I have been using some form of auto-saving a copy of this workbook for over a decade and this is what I have developed to overcome the problems encountered with malformed code, malware attacks, numerous clean reinstalls of Windows, and hard drive failures and changes

If you need to automatically delete excess backup, you can use the Dir function with the Date Created attribute and delete those created before a time period earlier then NOW().

As an Option, you can add this line to the start of the MakeCopy sub
If MsgBox("Make a Backup") = vbCancel Then Exit Sub


Option Explicit

Private Const BackUpDrive As String = "E:"
Private Const BackUpFolder As String = "MyPersonal"
Private Const BackUpName As String = "Personal.xls.Bak"



Private Sub Workbook_BeforeClose(Cancel As Boolean)
RestoreApplication

End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
MakeCopyOfPersonal

End Sub



Sub RestoreApplication()
'Used because I work with so many bad OP created files
With Application
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.CutCopyMode = False
.ScreenUpdating = True
End With
End Sub


Private Sub MakeCopyOfPersonal()
Dim TimeStamp As String

If Len(Dir(BackUpDrive & "\" & BackUpFolder, 16)) = 0 Then
MsgBox "Personal xls not backed up. Cannot find the Backup Folder"
Exit Sub
End If

TimeStamp = Left(CStr(CDbl(Now)), 10) & "_"
Me.SaveCopyAs (BackUpDrive & "\" & BackUpFolder & "\" _
& TimeStamp & BackUpName)

End Sub

Yongle
05-28-2015, 09:45 AM
another possibility for you
This code saves the original file when you close and then saves a copy of the file as OriginalFileName-Backup.xlsm
It will overwrite the original backup without asking for confirmation.
You can direct it to a different folder by adding a path before "BackupName"



Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.save
Dim BackupName As String
BackupName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1) & "-Backup.xlsm"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=BackupName
Application.DisplayAlerts = True
ActiveWorkbook.Close
End Sub


NB - the above code will save the file and overwrite the backup every time you close the file

Yongle
05-28-2015, 09:48 AM
It is possible that you do not want to save the file (perhaps you have made a mistake and just want to close the file without saving)
In which case this code would be better.
This will create a backup of your file only after you have saved it.


Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim BackupName As String
BackupName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1) & "-Backup.xlsm"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=BackupName
Application.DisplayAlerts = True
ActiveWorkbook.Close
End Sub

Yongle
05-28-2015, 10:34 AM
Do not put both those codes (posts #05 and #06) in the same file otherwise it will result in a recursive loop and you will end up with a backup-of the backup of the backup.... etc
This is because the the file is saved by the one macro as a backup and then the other macro saves it again as backup and then the other macro kicks in again...

DarinM
05-28-2015, 12:04 PM
Hey,

thanks for your solutions so far.

I have just noticed that there is a backup file... says "Backup of MasterTracker2015" above my file...looks like the excel automated is working?? I was just hoping to put it into my own file (i.e. backup folder, in the same main folder as the real tracker).

I think the default excel backup is going to work for me...

SamT
05-28-2015, 07:08 PM
I am going to click on the "Thread Tools" menu at the top of the page and mark this thread solved. You can do the same, to unmark it "solved."

Yongle
05-28-2015, 10:40 PM
:banghead:

SamT
05-29-2015, 08:35 AM
:rofl: