Thursday, December 17, 2009

Enhance Excel's Autosave with a File Syncing Service

I love this tip because I invented it.  I suppose other people might do the same thing, but it would be a parallel evolution of ideas as opposed to me looking at what other Excel blogs were writing about a year ago, adding swear words, and calling it an original post.

If you use Excel extensively, you know that it has the potential to lock up or crash.  A lot.  This mostly happens to me when I'm using formula auditing, but I've also been the victim of unprovoked crashes on multiple occasions.  Losing over an hour of work is pretty much a day-ending and possibly career-ending event for me as my only tools for coping with adversity are random, shocking violence and destruction of property, so I go to great lengths to make sure this never happens.

Fortunately, Excel has an autosave feature.  Unfortunately, this feature sucks.  Depending on how Excel crashed and how you restarted it, you may or may not be given the list of recoverable documents.  Also, despite setting the autosave interval as low as 10 minutes, you'll have inexplicable gaps in coverage.  Browsing to the directory where autosaves are kept can help, but Excel seems to clean this directory out pretty regularly.

What is one to do?  Well, you should be backing up the autosave directory remotely and instantly, giving you access to every version of your Excel documents going back as far as your storage space will allow.  There are a number of programs that do this, many of them free.  A lot of people like to use Drop Box for file syncing, but Drop Box only backs up one folder unless you hack it with odd shortcuts.  I prefer SugarSync.  SugarSync can back up or sync any folder you want either with your online account only or between multiple computers.  SugarSync is free to sign up for, but if you do it with this link I get a free storage upgrade.  Please?

I'm not going to go into the details of how to set up SugarSync, suffice to say it isn't brain science.  Once you do have it set up, you need to locate your autosave directory.  In Excel, go to Tools > Options > Save.

The AutoRecover save location is the directory you want.  Keep this path handy, as SugarSync forces you to browse to the location instead of just pasting it in.  Also, make sure that autosave is enabled with a time interval that you're comfortable with.

Now in SugarSync go to Manage Sync Folders and click on Add Folders from this Computer.  From there, browse to the backup folder, usually C:\Documents and Settings\Your User Name\Application Data\Microsoft\Excel\, and check the box next to it, then hit okay.  Congratulations, you'll never lose more than x minutes of work in Excel ever again.

You may notice though that this folder is pretty empty.  It actually syncs up with the local folder, so it's deleting files at the same time Excel is.  It hangs on to all of these files in a deleted files folder though, so you should have every version going back quite a ways.  I think SugarSync hangs onto the files until you run out of space; I have files going back to mid-November when I last cleaned it out.

The bad thing about this system?  Well for starters you can't tell what the file is for sure until you open it because Excel assigns it a gibberish name.  If you have a rough idea of the size of the workbook and when you were last working on the version you want though, you shouldn't have too hard of a time.  The other problem is that really large files that you're working on over the course of the day will back up dozens of times and that can eat into your free space pretty quickly.  You could always register for more space, which brings me to the last negative:  SugarSync's business model may not be profitable, and they might not be around forever.  I'm sure they'll give you advance notice if the service is going down though so you don't lose all of your data, and there will always be some other free option out there to replace it, so this shouldn't be a big deal.

The great thing about this system?  It isn't just protecting you from crashes, it's protecting you from yourself.  Have you ever started working on an old workbook as a template but then hit save instead of save as, unintentionally destroying the original?  Have you ever screwed up a workbook so completely that you need to go back in time and start over?  Have you ever had a workbook out on your network mysteriously vanish?  Have you ever just plain forgotten to save a file?  If you haven't, it's probably because you're young and your brain hasn't been ravaged by alcohol, drugs, reality television, and old age.  Your probably aren't even burnt out on your job yet!  Ah, sweet ignorance... but for the rest of us, this is a great back up plan and it's saved my bacon more than I care to admit.

Also, once you have SugarSync up and running, you can go crazy synchronizing mp3s, your personal macro workbook, plug-ins, and all sorts of other stuff between your work and home computers.  It'll be like you never even left the office!


No comments:

Post a Comment

Creative Commons License
David @ Work by David Montgomery is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License.