Automatically backing up your Excel files

Perhaps you have a spreadsheet that is absolutely critical?  Something that, if lost or corrupted, would end your world.  Maybe it contains rankings for your favourite Star Trek episodes (TNG:E5x25 “The Inner Light” FWIW)  or something more trivial like the company’s finances?

Either way, it would be a good idea to back it up to protect against both loss and ballsing up some convoluted formulas you wrote, but the chances of you being able to remember to do this regularly are pretty much nil.

The sensible person might ask why store such business-critical data in a spreadsheet, rather than in a server environment with automatic backup.  Such people haven’t always had the pleasure of working in organisations where the TV is referred to as “thar’ magic box o’light pixies”.

Instead of such trickery, you can instruct the file to back itself up.  In this instance, I prefer to have the file make a copy of itself every time I save.  You may prefer to use a different workbook event.

Add the following into the ThisWorkbook object:

Then add, beneath it, the following routine:

Over time, this will create an ordered series of snapshots of your file.  Should you make an error and save your file, you can be assured that you will have an earlier version on hand.

Who’s fault was it…!?!?!!!?

The filename string has been set to pull through the year, month, day, time (including seconds) and the Windows username of the person who committed the save.  This, in effect, creates an organised list of files.  American users may wish to rearrange month and day in “FullPath” to match their incorrect method of date formatting.

The username is useful for working out who to fire when a mistake is made.  Names have been pixelated to protect the guilty.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Leave a reply by filling in your details below, or connect automatically with social media:

Your email address will not be published. Required fields are marked *