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

[dwilbank]

Believe me, I've looked at Mr. Excel macros galore and several backup programs.
Couldn't find any that will do this, although it has to be a simple job for the right programmer.

I'm just looking for something that will dump a backup excel file to a predetermined location, regardless of where we choose to save the main file.
We save excel files all over our network drives, but would like to have a local place where copies go, automatically.

Obviously, people might want to use it for files other than .xls, but having a filter to copy JUST excel files is what I would need.

Is this harder than I think?

Thanks

[skwire]

Is this harder than I think?

Yes and no.  There are a lot of variables at play here.  Off the top of my head:

1) Does everybody use the same network drive i.e. there is only one network drive?
2) Are all XLS files saved to the network drive?  What about XLS files saved on a user's local computer?
3) How to handle version history of the copied XLS files, if any?
4) How to handle filename collisions?  Does each user get their own subfolder in the destination folder, etc.?
5) ...

There is potential for a large amount of complexity here depending on what you're after.  I suppose what I'm getting at is that you're going to need to provide a lot more in the way of specifics.

[tranglos]

I'm just looking for something that will dump a backup excel file to a predetermined location, regardless of where we choose to save the main file.
We save excel files all over our network drives, but would like to have a local place where copies go, automatically.

Have you considered any of the real-time backup applications? They monitor any folders you specify (could be the whole drive, but check for performance before you commit) and any time a file is changed, it gets copied to another folder, usually with a timestamp. You could try FileHamster (more advanced options, more resource usage) or AJC Active Backup (fewer options, much leaner). You can configure either program to backup Excel files only, or whatever other types of files you wish.

Of course, check whether any program you choose supports monitoring network drives. This may not always work, and may depend on the network architecture or maybe filesystems used on the networked drives. I'm a little fuzzy on the particulars here, but I've found out in my programming adventures that standard change notifications APIs don't seem to work for (some?) network drives. Worst case, you could write a script that combs through all network drives every so often and makes backups of any new Excel files it finds. Most standard (not real-time) backup apps will happily do that out of the box.

And incidentally, Excel happens to be one of the hardest cases for real-time backup, due to an incomprehensibly(*) weird behavior. When you open an .xls file, Excel immediately changes its date/time to "now" and changes a few bytes in the header. If you close the file without making any modification, Excel restores the original timestamp, but the changed bytes remain. Now, to a backup program, it looks like the file has changed twice, even though you haven't touched it at all. So some backup programs will make two new copies of every Excel doc that you open. This isn't a tragedy, but it's wasted disk space. As far as I can tell, of the two apps mentioned above, FileHamster has some special code for Excel files and doesn't make those unnecessary copies of unchanged files. AJC on the other hand lets itself be fooled by Excel and does store surplus colpies of unmodified xls files.

(*) I have no idea why Excel could possibly be doing that. If it's to tamper-proof files, it's laughably easy to defeat - but it does defeat regular backup strategies. Go figure.

[AndyM]

We save excel files all over our network drives, but would like to have a local place where copies go, automatically.

I know you said you looked at a VBA solution but since you are only needing this when running Excel, it would be simplest to deal with it as part of the File Save process.  You could write a simple script that would both save the file normally and then copy that file to a specific location.  Assign it to Ctrl-S and/or a toolbar button and/or a menu choice.

[dwilbank]

I could certainly use a simple script that would intercept the Ctrl-S or the F12 Save As command...
(Now I know this is starting to sound like spyware, but I only want it to provide backups for all our work on all our shifts -- without having to change the workflow at all.)

My computer is the only one that creates these specific XLS files, and we always write to the same network drive.
We write to a different folder every day though, (new folders are created for every job we do) and I only want MY XLS files to be backed up... not everyone else's - which is why I can't use a standard folder monitoring app.

[AndyM]

So the only files you want to copy to a fixed additional location are the Excel files that you create, and you want them backed up every time anyone in the company saves a modified version of your file regardless of what folder that modified version is being saved to (differing job folders).

Now it really seems like the way to go is to intercept the Save and SaveAs commands.  Since it's only certain of your spreadsheets you want this to apply to, you have complete control over the process by doing it this way.  What's the downside?

--------------------
Edit:  I re-read your last post and now I'm not sure what you mean by "I only want MY XLS files to be backed up... not everyone else's".

Do you mean only the versions of the original file that you save, or the versions of the file you created that others save?

[mouser]

i still think Tranglos' suggestion might be the simplest and most foolproof -- use one of the many programs that are intended to monitor folders you specify and make backups of files any time they change.

[AndyM]

For a different approach than intercepting the Save commands, check out the WorkbookBeforeSave Event

[dwilbank]

AndyM -- I only want my own work to be backed up - not anyone else's changes.
The problem is that other folks take the files that I create and alter them, or even worse, lose them!
I just want an untouched backup to be created everytime I choose Save, or Save As.

Mouser - I can look again at FileHamster, et al, but since every new file I create is for a new TV show or movie, new directories are created every day.

So if I tell Filehamster to monitor "G:\Fox\NTSC\X-Files Season 2\", how will it do its job when I save a file the same day to "G:\WHV\Clone Wars\PAL\Season 2"?

Also, other people are creating unrelated xls files in all these directories all day, which have nothing to do with my work. I surely don't want to create local copies of those...

The downside of intercepting the save and save-as commands is that I don't know VBA!
I looked through Mr. Excel to try to find a preexisting macro. I certainly couldn't make one myself!

[rjbull]

Could you use one of the command-line archiver programs, telling it to store all XLS files complete with path and starting from the root, then rename the resulting archive with today's date and time?  And do that as a scheduled event?

Of course, I probably haven't understood the situation... 

[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.

Code

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

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.

[dwilbank]

But if I'm altering the file 20 times and hitting F12 after each change and giving each alteration a new file name, I'll still get 20 legit backups?

I'll test it soon to make sure. Thx

[AndyM]

If you're main method of cloning your files is "Save As", then the script needs work.