DonationCoder.com Software > Post New Requests Here

IDEA: app which creates instant backup of any file you save in excel

<< < (3/4) > >>

AndyM:
When I work on something like this it is almost always for myself - if I break something either I fix it or I suffer.  Playing with files (saving, copying, archiving, etc.) can lead to problems, so back up everything and work with copies until you are confident.

If you put this code in ThisWorkbook, every time you save a file it will first make a copy of the file in D:\xscr, regardless of where you are putting the original.  You would have to change "D:\xscr" to whatever location you wish to use for your backup copies.


--- ---Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim CurFile As String
Dim BUFolder As String
CurFile = Application.ActiveWorkbook.Name
BUFolder = "D:\xscr"
On Error Resume Next
ActiveWorkbook.SaveCopyAs Filename:=BUFolder & "\" & CurFile
End Sub
 
I haven't tested this much (haven't tried it on a file already in D:\xscr).
If you invoke the SaveAs dialog, it still saves a backup copy with the original name.  When you next save the renamed file it will be backed up.  There's a better way to deal with SaveAs but if it waits for me to fool with it, I may never post this.

I don't know VBA
--- End quote ---
So you probably don't know where ThisWorkbook is  :)
Open the Visual Basic Editor (Alt-F11).
You'll see a file tree of sorts on the left.  Find your open file, and one of the items on the sublist will be This Workbook.  Click on it to open the ThisWorkbook window, paste the code in and off you go.  This code will be saved with the file, and the backup will be made if "D:\xscr" exists.  The On Error command should (I think) prevent error messages that others would have when saving the file if they don't have a folder names "D:\xscr".

If you do much work in Excel, learning some VBA and the way the VB Editor works would be worth your while.  Most of the techniques also apply to Word and other Office programs.

Btw, since the backup file is overwritten with the new file every time you save, you only have the latest version backed up.  This is fine if there's a new filename every day.  But if it's the same filename over and over, just in different locations, this scheme is inferior to one in which you save versions.

dwilbank:
Can't wait to try it out - thx!

dwilbank:
It worked!
Now just let them try to lose my xls files and make me redo them!

Thanks

AndyM:
Glad it worked  :)

(check if a space in a file name causes a problem)

AndyM:
It occurs to me that if, on Tuesday, you are starting with Monday.xls (already backed up on Monday), and then renaming it to Tuesday.xls by using Excel's "File>Save As" dialog, you should do that as your very first step before making any Tuesday changes, since this simple script will save & overwrite your Monday.xls backup file just before the Save As Tuesday.xls operation.

It should still be a proper Monday.xls backup, but it will have Tuesdays Modified Date.  But it won't be a proper Monday.xls if you change something before doing Save As Tuesday.xls.

Personally I would make a copy of Monday.xls in Explorer and rename it Tuesday.xls and then open the Tuesday.xls file.  Then your autobackup script will perform as desired without changing Monday.xls or the backup of Monday.xls.

Navigation

[0] Message Index

[#] Next page

[*] Previous page