As any Excel VBA ‘Desk Jockey’ can tell you, the hours melt away when you are coding. Yesterday I was developing some HTML for my website tool and in the ‘wink of an eye’ the morning was gone. Getting the look and feel right was quite a challenge but rewarding when completed.
As a VBA freelancer and experienced auditor I can attest to the complexity of some of the Excel workbooks out there.  Some are truly works of art, many more functional, either way they represent hundreds of hours of work and are assets that need protection.
When I first created the backup tool I used a simple $15 thumb drive. I recently upgraded to a portable 1 terabyte portable hardrive for $120. My point is for a few dollars you can buy some great insurance that your work will be there should the unthinkable happen. 

Have you ever had a workbook just disappeared? Maybe you did something dumb and overwrote it. How about a PC catastrophic failure? I love the ASUS laptop I am typing on, but it is a couple years old. it is always one cup of coffee/water away from tragedy Yes I know Excel as a backup feature, but I wanted more.  The File Backup Tool is my answer to that desire. It features:
n Point and click file selection (any type of file)
n Updates Backups for only files that have been revised (efficient)
n Runs on demand and/or when you close Excel
n Provides a note area where you can leave key details on the file 

Key File Backup Tool components

File Name:
Date Created:
Last Updated:
Last Backup:
BU Path:
The name of the file to be backed up 
The path where that file can be found
The Date the file was created
The Latest file revision Date
The last time the tool backed up the file
Size in bytes
Where the file will be backed up. (my external drive in this case)
Great place to include details that you may need later.

All but the File Details textbox are auto populated when you use the browse buttons to select your files and BU directory.  The lower section with the tabs list the backup files with the last revison and backup dates.  
You’ll note:
1) The red dots indicate a file that will be backed up.   
2) You can hover over any file to see the details about the file.
3) The only field that you can manually modify is the File Details     

Steps to Use the Key File Backup Tool
1. Download both the Key File backup.xlsm and the Auto Backups.accdb files into the same directory and make note of that directory you will need that later.
2. Open the VBE (Visual Basic Editor) if you havent used VBA before see Here
Then use the Visual Basic button on the far left to in the developer tab to open the VBE 
3. add the following to your Personal.xlsb file in the ThisWorkbook object code module – it should look like this:
4. Invoke the tool by opening the workbook and simutaneously hitting the Ctrl +k keys 

Do some test backups and make sure the is backing the files up as expected!

Change The “Directory from step 1” to the directory you created in step 1

Be sure and hit the little blue save diskette or cntrl +s to save the changes to your personal workbook

You can download the Tool here:

We ask for your email address so we can send you any updates to the code. We do not ever share emails addresses and take privacy very seriously.


Ray Mills
[sdm-squeeze-form id=3172 fancy=”0″ button_text="Download Now"]
If you were able to utilize what you learned here or you improved the code, please leave a comment Here

If you enjoyed this post or found it helpful andl would like to say hello and buy me a cup of coffee
Click here

Raymond Mills, M.B.A., M.S.  has spent over 20 years of his career as Accountant, Investment Bank and Credit Card Technical Auditor/ Data Analyst.  His specialty was using Excel to get Big Databases including Teradata, Oracle,  Squel Server and Sybase to give up their secrets.
Ray has said “I love nothing better than using VBA to unleash the power of Microsoft Office.” You can contact Ray @ 484 574-3190 or by emailing him Here

If you have a challenge with Excel, Access or Word and would like to speak with Ray,   You can get his contact details by clicking here: Contact Me