Welcome Guest.   Make a donation to an author on the site September 18, 2014, 04:39:19 AM  *

Please login or register.
Or did you miss your validation email?


Login with username and password (forgot your password?)
Why not become a lifetime supporting member of the site with a one-time donation of any amount? Your donation entitles you to a ton of additional benefits, including access to exclusive discounts and downloads, the ability to enter monthly free software drawings, and a single non-expiring license key for all of our programs.


You must sign up here before you can post and access some areas of the site. Registration is totally free and confidential.
 
The N.A.N.Y. Challenge 2013! Download dozens of custom programs!
   
   Forum Home   Thread Marks Chat! Downloads Search Login Register  
Pages: [1]   Go Down
  Reply  |  New Topic  |  Print  
Author Topic: IDEA: app which creates instant backup of any file you save in excel  (Read 5137 times)
dwilbank
Supporting Member
**
Posts: 76

View Profile Give some DonationCredits to this forum member
« on: September 18, 2009, 02:40:46 PM »

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
Logged
skwire
Moderator
*****
Posts: 4,072



Another Coding Snack request? Om nom nom...

see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #1 on: September 18, 2009, 03:30:40 PM »

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

tranglos
Supporting Member
**
Posts: 1,079



see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #2 on: September 18, 2009, 06:07:37 PM »

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.
« Last Edit: September 18, 2009, 06:13:35 PM by tranglos » Logged

AndyM
Charter Member
***
Posts: 616


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #3 on: September 18, 2009, 08:37:49 PM »

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.
« Last Edit: September 18, 2009, 08:39:28 PM by AndyM » Logged
dwilbank
Supporting Member
**
Posts: 76

View Profile Give some DonationCredits to this forum member
« Reply #4 on: September 19, 2009, 02:19:27 AM »

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.
Logged
AndyM
Charter Member
***
Posts: 616


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #5 on: September 19, 2009, 10:19:19 AM »

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?
« Last Edit: September 19, 2009, 12:25:06 PM by AndyM » Logged
mouser
First Author
Administrator
*****
Posts: 33,357



see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #6 on: September 19, 2009, 12:00:40 PM »

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.
Logged
AndyM
Charter Member
***
Posts: 616


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #7 on: September 19, 2009, 12:25:53 PM »

For a different approach than intercepting the Save commands, check out the WorkbookBeforeSave Event
Logged
dwilbank
Supporting Member
**
Posts: 76

View Profile Give some DonationCredits to this forum member
« Reply #8 on: September 19, 2009, 02:53:53 PM »

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!
Logged
rjbull
Charter Member
***
Posts: 2,758

View Profile Give some DonationCredits to this forum member
« Reply #9 on: September 19, 2009, 04:14:07 PM »

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...  Sad
Logged
AndyM
Charter Member
***
Posts: 616


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #10 on: September 19, 2009, 06:03:34 PM »

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.

[copy or print]
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.

Quote
I don't know VBA
So you probably don't know where ThisWorkbook is  smiley
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.
« Last Edit: September 19, 2009, 06:06:41 PM by AndyM » Logged
dwilbank
Supporting Member
**
Posts: 76

View Profile Give some DonationCredits to this forum member
« Reply #11 on: September 20, 2009, 06:34:28 PM »

Can't wait to try it out - thx!
Logged
dwilbank
Supporting Member
**
Posts: 76

View Profile Give some DonationCredits to this forum member
« Reply #12 on: September 21, 2009, 12:09:16 PM »

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

Thanks
Logged
AndyM
Charter Member
***
Posts: 616


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #13 on: September 21, 2009, 12:15:50 PM »

Glad it worked  smiley

(check if a space in a file name causes a problem)
« Last Edit: September 21, 2009, 12:18:40 PM by AndyM » Logged
AndyM
Charter Member
***
Posts: 616


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #14 on: September 21, 2009, 12:28:06 PM »

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.
« Last Edit: September 21, 2009, 12:30:02 PM by AndyM » Logged
dwilbank
Supporting Member
**
Posts: 76

View Profile Give some DonationCredits to this forum member
« Reply #15 on: September 22, 2009, 07:10:30 PM »

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
Logged
AndyM
Charter Member
***
Posts: 616


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #16 on: September 22, 2009, 07:25:45 PM »

If you're main method of cloning your files is "Save As", then the script needs work.
Logged
Pages: [1]   Go Up
  Reply  |  New Topic  |  Print  
 
Jump to:  
   Forum Home   Thread Marks Chat! Downloads Search Login Register  

DonationCoder.com | About Us
DonationCoder.com Forum | Powered by SMF
[ Page time: 0.048s | Server load: 0.19 ]