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: Path: Date Created: Last Updated: Last Backup: Size: BU Path: Notes: | 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!
Be sure and hit the little blue save diskette or cntrl +s to save the changes to your personal workbook
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
If you enjoyed this post or found it helpful andl would like to say hello and buy me a cup of coffee
Click here
