A VBA-Based Backup Solution
June 11, 2009 • JP • No Comments • Rate This Article
• Links to this article
Problem: You need an easy way to backup an important file on a regular basis. Copying and pasting the file to another folder (with Windows Explorer or some other folder browsing tool), or saving it twice (once to its current folder, and again to a backup folder) is tedious, and still requires you to manually indicate file version.
Solution: Use this Excel VBA procedure. It can be used to backup any single file to a folder of your choice, appending a timestamp to the filename for easy rollback and version management.
The way it works is simple. First you are prompted to select any single file. It doesn't have to be an Office file (xls, ppt, doc, etc), it can be any file type. The file name and extension are extracted from the full filepath. Then you are prompted for the destination of the backup file. Once that is selected, a copy of the original file, complete with timestamp, is copied to your chosen folder.
To browse for a folder to place the backup file, I was using a procedure which you can find at Save Incoming Attachments, Choose Your Folder, but have since started using the one at VBA Express. You'll need to copy and paste the code into the same module along with the code below.
' make a copy of any file in any folder
' pad with space
Const BACKUP_FILE As String = " BACKUP"
' get current date and time, padded with space
Dim todaysDate As String
todaysDate = " " & Format(Now, "MMDDYYYY HHMM")
' get full filepath of file to be copied
Dim fileName As String
fileName = GetFileName
If fileName = "" Then
MsgBox "No file selected, exiting now."
Exit Sub
End If
' get file extension
Dim fileType As String
fileType = GetFileType(fileName)
' extract filename from drive letter and extension
Dim extractedFileName As String
extractedFileName = ExtractFileName(fileName)
' browse for folder to place files
Dim fileFolder As String
fileFolder = BrowseForFolder("H:\")
If fileFolder = "False" Then
MsgBox "No folder chosen, no backup is being made."
Exit Sub
End If
' copy file to folder, complete with new name
FileCopy fileName, fileFolder & extractedFileName & BACKUP_FILE & todaysDate & fileType
End Sub
Function GetFileName() As String
Dim fileName As String
fileName = Application.GetOpenFilename("All Files (*.*), *.*", , "Choose File to Backup")
If fileName <> "False" Then
GetFileName = fileName
End If
End Function
Function ExtractFileName(fileName As String) As String
' extract filename portion of filename, no extension
Dim fileN As String
fileN = Right(fileName, Len(fileName) - InStrRev(fileName, "\"))
fileN = Replace(fileN, GetFileType(fileN), "")
ExtractFileName = fileN
End Function
Function GetFileType(fileName As String) As String
' get file extension
GetFileType = Mid$(fileName, InStrRev(fileName, "."), Len(fileName))
End Function
There are a couple of custom functions called from the main procedure. The Application.GetOpenFilename method is encapsulated in the GetFileName() function. The GetFileType() function extracts the file extension from the filename, so we can append it at the end later, after adding the timestamp. To get the filename out of the filepath, ExtractFileName takes out the file extension and the path up to the last backslash.
BrowseForFolder is used to get the destination folder, so make sure you visit VBA Express and get the code.
The FileCopy method takes two arguments: the file to be copied (including path), and the destination filename (including path). So it's a simple matter of creating a string containing the new path, the original filename and an added string containing the word "BACKUP" and a timestamp.
It's important that the code not be placed inside the file you want to back up, since FileCopy cannot work on an open file.
Another idea is to put the code inside the Workbook_Open Event for a separately maintained Excel workbook, then use Windows Task Scheduler to open the workbook every night (using command line switches). Assuming, of course, that you keep your computer on and logged in all night. Once opened, the Workbook_Open event will fire, and your backup code will execute. You would also need to edit the procedure and hard code information such as path and filename into it, since nobody will be around to browse for the appropriate files and folders. But in lieu of a "real" backup solution, this works.
VBA ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Reminder: Advanced Excel Conference
Next Post: Unbind an Access Form
Years ago, at my last real job (at least, the last real job I didn't totally hate), I used a similar set of procedures to back up my main work directories to a network share.
The program used a two-column list of source and destination pathnames, and the code stepped through it, recursively to include subdirectories, and backed up all files with a newer saved date. If the folder had an empty text file named NoBackUp, then the directory was skipped.
I should dig that out again. It was pretty slick.
Don't hold back, share away!
I'm not holding back. I haven't used or even looked at that code in around 8 years. I know I have it, but I don't know offhand where it might be.
Jon
Had any luck finding that code?? I'd be very interested.
Thanks JP, for this and the follow up. Very relevant at the moment.
Lincoln -
Found the code. I need to clean it up a bit. I wrote it long ago, before I was good enough to turn pro.