Versioning Excel Files

Versioning is the best way to prevent data loss in case of software crashing or faulty updates. If you are a  Microsoft 365 subscriber, the AutoSave feature handles getting copies frequently and allows you to access previous autosaved versions. If you are not a subscriber or want to keep your copies in your local storage, VBA is your friend. In this guide, we’re going to show you Versioning Excel files.

Download Workbook

Microsoft 365 and AutoSave

Microsoft 365's AutoSave feature saves your files automatically in a few seconds intervals while you are working. Do not hesitate to use it if you are OK to keep your file in cloud storage. Although it's hard to miss it, you can enable AutoSave by turning the AutoSave switch on. The switch is at the left of the Excel window's title.

You can access the previous version by clicking the File > Info > Version History button.

The command displays the versions with timestamps in a panel on the right side. Click on the one you want to return.

Versioning Excel Files with VBA

Since VBA allows us to save our files with the names we want, we can create our own versioning feature. Thanks to VBA, we will have full control of saving frequency, file name, and location.

Create Versions Manually

The following code saves both the active workbook and a copy of it by adding the date and time to the file name. The date is added to the file name in year-month-day format. The Format function of VBA is similar to Excel's TEXT function. Thus, do not hesitate to modify the timestamp according to your needs.

Although it is not mandatory, the code contains codes for error handling as well. The important part starts after the comment "Save a version with timestamp".

You need to run this manually anytime you want to get a backup.

Sub SaveWithTimeStamp()
    'https://www.spreadsheetweb.com

    'Go to error handling code in case of an exception
    On Error GoTo ErrorHandler

    'Disable alerts to avoid user interaction
    Application.DisplayAlerts = False

    'Save the file with same name
    ActiveWorkbook.Save

    'Save a version with timestamp
    ''' Define variables
    Dim arr() As String, ext As String, filename As String
    ''' Parse file name and extension
    arr = Split(ActiveWorkbook.Name, ".")
    ext = arr(UBound(arr))
    filename = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))
    ''' Save a copy of the file with a timestamp
    ActiveWorkbook.SaveCopyAs _
        ActiveWorkbook.Path & "\" & filename & " - " & Format(Now(), "yyyy-MM-dd hh-mm-ss") & "." & ext

    ' Settings before exit
ExitSub:
    'Enable alerts to avoid user interaction
    Application.DisplayAlerts = True
    Exit Sub

    'Error handling code
ErrorHandler:
    MsgBox Prompt:="Error Code: " & Err.Number & Chr(10) & Err.Description, Title:="Error occured!"
    Err.Clear
    GoTo ExitSub
End Sub

Autosaving Periodically

Let's say you want the versioning code to be executed at specific intervals. Two simple steps are needed.

First, add the following line to your code.

Application.OnTime Now + TimeValue("00:05:00"), "SaveWithTimeStamp"

This line runs the macro named "SaveWithTimeStamp" in every 5 minutes. Do not forget to change the macro name (SaveWithTimeStamps) if yours is different. You can change the interval by changing the time string in TimeValue method.

The second action you need to take is to add a three-line code to the ThisWorkbook page. This code will save you from manually executing the code for the first time.

Code in Workbook_Open

Private Sub Workbook_Open()
    ' Execute my code at every 5 minutes
    Application.OnTime Now + TimeValue("00:05:00"), "SaveWithTimeStamp"
End Sub

To learn more about running a macro at a specific time, please check How to run macro at a specified time in Excel article.