Copy Workbook After Save (VBA Event)

Introduction

During the last week, I was working on a VBA project related to dbf files. I spent several hours on code development trying to fix various errors that came up. I created several versions of the code until I feel satisfied with the result. Quite quickly I realized that I have to automate the “saving procedure”. What is a “saving procedure”?

Well, a good programming practice, either you writing VBA or other code, is to save your file just before running the code. In this way, if the code/macro causes something unexpected to your workbook (i.e. deletes a sheet/cell etc.), you can simply close the workbook without saving it, and, then, re-open it; changes occurred by the macro will be disregarded, while your VBA code will be “safe”.

Another typical programming practice is to comment out – to “deactivate” a block of code by turning it into a comment. Developers prefer this technique because at any time they can “reactivate” the commented lines and use them normally in the code. This is very helpful when the developer is seeking the optimal solution to an algorithm problem, so he/she tries a solution, then he/she comment out the solution and tries another one (without losing the lines of code that produced the initial solution).

Even if you use both techniques during code development there are times that you will fill confused about which lines produced a working solution. Consider the following scenario: you found an initial solution to your programming problem. However, you believe that this solution is not the optimal one, so you try something else. Your last attempt did not work, so you try another one, and another one, and so on. After some attempts, it is quite possible to have forgotten your initial solution, while your source code will be filled with various comment lines.

To avoid the above scenario I wrote a small workbook event procedure (see the VBA code below), which helped me avoid “confusing situations” the previous days. The code uses the Workbook_AfterSave event to copy the saved workbook into a specified folder, adding the current date and time at the “copied” workbook path.

For example, the
C:\Users\Christos\Desktop\Copy Workbook After Save (Excel VBA Event).xlsm
file will be saved as:
C:\Users\Christos\Desktop\Tests\Copy Workbook After Save (Excel VBA Event) (2013-12-26 – 15-34-24).xlsm

With the procedure below, whenever the user/developer presses the Save button, the workbook and the associated VBA code will be saved, and then the workbook will be copied to a safe location. Each time the user saves the workbook, a new version will be created. If something unexpected occurs, the user will have all the saved versions as back-up, so he will lose nothing! Moreover, if he/she gets confused about which lines of code produce a working solution, he/she can simply run the code of the various back-up workbooks; each back-up workbook will contain the VBA code written until the moment it was saved.

VBA code to copy workbook after save – event

The code below uses the SaveCopyAs method to automate the Workbook_AfterSave event. To use the procedure below, select and copy all the code, open Excel, switch to VBA editor (ALT + F11), select the ThisWorkbook object of your file, and paste the code.

Option Explicit

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    
    '---------------------------------------------------------------------------------------------
    'When the user presses the Save button the macro copies the saved workbook into the selected
    'folder (DestinationFolder) for back-up, adding the current date and time in the file's path.
            
    'Written By:    Christos Samaras
    'Date:          26/12/2013
    'E-mail:        [email protected]
    'Site:          https://myengineeringworld.net
    '---------------------------------------------------------------------------------------------
    
    'Declaring the necessary variables.
    Dim DestinationFolder   As String
    Dim WbName              As String
    Dim WbExtension         As String
    Dim WbNewPath           As String
    
    'Change the folder's path according to your needs.
    DestinationFolder = "C:\Users\Christos\Desktop\Tests"
    
    'Check if the destination folder exists.
    If DestinationFolder = "" Or Dir(DestinationFolder, vbDirectory) = vbNullString Then
        MsgBox "The destination folder's path is incorect!", vbCritical, "Wrong folder's path"
        Exit Sub
    End If
        
    'Get the workbook name without the extension.
    WbName = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1))
    
    'Get the workbook extension.
    WbExtension = Right$(ThisWorkbook.Name, Len(ThisWorkbook.Name) - InStrRev(ThisWorkbook.Name, "."))
    
    'Create the new workbook path - add the current date/time at the end.
    WbNewPath = DestinationFolder & "\" & WbName & " (" & Format(Now(), "yyyy-mm-dd - hh-mm-ss") & ")." & WbExtension
    
    'Copy the current workbook into the destination folder.
    ThisWorkbook.SaveCopyAs WbNewPath
    
End Sub 

I hope that VBA developers will appreciate this small event procedure.

Downloads

The file can be opened with Excel 2007 or newer. Please enable macros before using it.