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.
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
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.