VBA Code To Save As A New Version If File Already Exists
Version Control
Keeping track of different versions of a file has always been a pain for me. I am grateful that I have access to Microsoft SharePoint at my company and am able to easily save a new version without even thinking. However I received a question from a reader who goes by the name Kauket, asking if it was possible to automate saving a new version for a file. This was a great question and really caught my attention. Let’s walk through how I personally would tackle this problem and see if we can come up with an efficient solution.
The Logic
Let’s first think through what we will need to code. Below I will list a few of the major capabilities our VBA code will need to possess.
- Determine if a previous version already exists
- Determine where the file is saved (or if it is saved at all)
- Create a new version with an incremental version extension (for example adding “_v2” to the end of the file name)
I decided to write VBA code for Excel as well as Miscrosoft Word and PowerPoint in case you would like to use the same process with your documents and presentations. Luckily there aren’t too many differences between the three macros sets.
Function To Determine If File Exists:
In order for the main VBA code to work, you will need to use a small function that determines if a specific file (via file path) exists on your computer. This is very important in order to determine the proper version number to use. I found a great article on Ron de Bruin’s website detailing various ways to check if something exists with VBA code. Be sure to check out his full article, Test if Folder, File or Sheet Exists or File is Open as it is a great read. The below function code is what I was able to glean from Ron’s article.
MAKE SURE that you include this function along with any of the application macro code versions shown below. You can just paste the below code below either the Excel, PowerPoint, or Word subroutines.
Function FileExist(FilePath As String) As Boolean
'PURPOSE: Test to see if a file exists or not
'SOURCE: www.TheSpreadsheetGuru.com/The-Code-Vault
'RESOURCE: http://www.rondebruin.nl/win/s9/win003.htm
Dim TestStr As String
'Test File Path (ie "C:\Users\Chris\Desktop\Test\book1.xlsm")
On Error Resume Next
TestStr = Dir(FilePath)
On Error GoTo 0
'Determine if File exists
If TestStr = "" Then
FileExist = False
Else
FileExist = True
End If
End FunctionVBA Code of Version Control For Excel
Let’s start with the Excel version of the VBA code, as I assume most of you reading are interested in using the version control capabilities within Excel (since you make up the bulk of my readers). The code has only one input that you may want to change and that is the code variable VersionExt. I made the extension default to “_v” (ie myReport_v2.xlsx) but you can just as easily change it to ” v” (ie myReport v2.xlsx) or any other extension you may want/need. Be sure to leave a comment below if you have any questions about making this tweak to the VBA code.
Please note that you will need the FileExist Function code (located above this section) pasted into your code module as well. If you need a walk-through on how to insert a macro into Microsoft Excel to use across any of your Excel Workbooks, you can check out an earlier post I wrote entitled How to Create A Personal Macro File.
Sub SaveNewVersion_Excel()
'PURPOSE: Save file, if already exists add a new version indicator to filename
'SOURCE: www.TheSpreadsheetGuru.com/The-Code-Vault
Dim FolderPath As String
Dim myPath As String
Dim SaveName As String
Dim SaveExt As String
Dim VersionExt As String
Dim Saved As Boolean
Dim x As Long
TestStr = ""
Saved = False
x = 2
'Version Indicator (change to liking)
VersionExt = "_v"
'Pull info about file
On Error GoTo NotSavedYet
myPath = ActiveWorkbook.FullName
myFileName = Mid(myPath, InStrRev(myPath, "\") + 1, InStrRev(myPath, ".") - InStrRev(myPath, "\") - 1)
FolderPath = Left(myPath, InStrRev(myPath, "\"))
SaveExt = "." & Right(myPath, Len(myPath) - InStrRev(myPath, "."))
On Error GoTo 0
'Determine Base File Name
If InStr(1, myFileName, VersionExt) > 1 Then
myArray = Split(myFileName, VersionExt)
SaveName = myArray(0)
Else
SaveName = myFileName
End If
'Test to see if file name already exists
If FileExist(FolderPath & SaveName & SaveExt) = False Then
ActiveWorkbook.SaveAs FolderPath & SaveName & SaveExt
Exit Sub
End If
'Need a new version made
Do While Saved = False
If FileExist(FolderPath & SaveName & VersionExt & x & SaveExt) = False Then
ActiveWorkbook.SaveAs FolderPath & SaveName & VersionExt & x & SaveExt
Saved = True
Else
x = x + 1
End If
Loop
'New version saved
MsgBox "New file version saved (version " & x & ")"
Exit Sub
'Error Handler
NotSavedYet:
MsgBox "This file has not been initially saved. " & _
"Cannot save a new version!", vbCritical, "Not Saved To Computer"
End SubVBA Code of Version Control For Microsoft Word
The only thing you need to change from the first Excel VBA code is to change ActiveWorkbook to ActiveDocument. Everything else is the same!
Please note that you will need the FileExist Function code (located near the beginning of this article) pasted into your code module as well. If you need a walk-through on how to insert a macro into Microsoft Word to use across any of your Word Documents, you can check out an earlier post I wrote entitled How to Create A Personal Macro File.
Sub SaveNewVersion_Word()
'PURPOSE: Save file, if already exists add a new version indicator to filename
'SOURCE: www.TheSpreadsheetGuru.com/The-Code-Vault
Dim FolderPath As String
Dim myPath As String
Dim SaveName As String
Dim SaveExt As String
Dim VersionExt As String
Dim Saved As Boolean
Dim x As Long
TestStr = ""
Saved = False
x = 2
'Version Indicator (change to liking)
VersionExt = "_v"
'Pull info about file
On Error GoTo NotSavedYet
myPath = ActiveDocument.FullName
myFileName = Mid(myPath, InStrRev(myPath, "\") + 1, InStrRev(myPath, ".") - InStrRev(myPath, "\") - 1)
FolderPath = Left(myPath, InStrRev(myPath, "\"))
SaveExt = "." & Right(myPath, Len(myPath) - InStrRev(myPath, "."))
On Error GoTo 0
'Determine Base File Name
If InStr(1, myFileName, VersionExt) > 1 Then
myArray = Split(myFileName, VersionExt)
SaveName = myArray(0)
Else
SaveName = myFileName
End If
'Test to see if file name already exists
If FileExist(FolderPath & SaveName & SaveExt) = False Then
ActiveDocument.SaveAs FolderPath & SaveName & SaveExt
Exit Sub
End If
'Need a new version made
Do While Saved = False
If FileExist(FolderPath & SaveName & VersionExt & x & SaveExt) = False Then
ActiveDocument.SaveAs FolderPath & SaveName & VersionExt & x & SaveExt
Saved = True
Else
x = x + 1
End If
Loop
'New version saved
MsgBox "New file version saved (version " & x & ")"
Exit Sub
'Error Handler
NotSavedYet:
MsgBox "This file has not been initially saved. " & _
"Cannot save a new version!", vbCritical, "Not Saved To Computer"
End SubVBA Code of Version Control For PowerPoint
Similar to the Microsoft Word version, the only thing you need to change from the first Excel VBA code is change ActiveWorkbook to ActivePresentation. Everything else is the same!
Please note that you will need the FileExist Function code (located near the beginning of this article) pasted into your code module as well. If you need a walk-through on how to insert a macro into PowerPoint to use across any of your presentations, you can check out an earlier post I wrote entitled How to Create A Personal Macro File.
Sub SaveNewVersion_PowerPoint()
'PURPOSE: Save file, if already exists add a new version indicator to filename
'SOURCE: www.TheSpreadsheetGuru.com/The-Code-Vault
Dim FolderPath As String
Dim myPath As String
Dim SaveName As String
Dim SaveExt As String
Dim VersionExt As String
Dim Saved As Boolean
Dim x As Long
TestStr = ""
Saved = False
x = 2
'Version Indicator (change to liking)
VersionExt = "_v"
'Pull info about file
On Error GoTo NotSavedYet
myPath = ActivePresentation.FullName
myFileName = Mid(myPath, InStrRev(myPath, "\") + 1, InStrRev(myPath, ".") - InStrRev(myPath, "\") - 1)
FolderPath = Left(myPath, InStrRev(myPath, "\"))
SaveExt = "." & Right(myPath, Len(myPath) - InStrRev(myPath, "."))
On Error GoTo 0
'Determine if file has ever been saved
If FolderPath = "" Then
MsgBox "This file has not been initially saved. " & _
"Cannot save a new version!", vbCritical, "Not Saved To Computer"
Exit Sub
End If
'Determine Base File Name
If InStr(1, myFileName, VersionExt) > 1 Then
myArray = Split(myFileName, VersionExt)
SaveName = myArray(0)
Else
SaveName = myFileName
End If
'Test to see if file name already exists
If FileExist(FolderPath & SaveName & SaveExt) = False Then
ActivePresentation.SaveAs FolderPath & SaveName & SaveExt
Exit Sub
End If
'Need a new version made
Do While Saved = False
If FileExist(FolderPath & SaveName & VersionExt & x & SaveExt) = False Then
ActivePresentation.SaveAs FolderPath & SaveName & VersionExt & x & SaveExt
Saved = True
Else
x = x + 1
End If
Loop
'New version saved
MsgBox "New file version saved (version " & x & ")"
Exit Sub
'Error Handler
NotSavedYet:
MsgBox "This file has not been initially saved. " & _
"Cannot save a new version!", vbCritical, "Not Saved To Computer"
End SubBe Prepared and Be Organized
I can’t tell you enough how important it is to keep multiple versions of a file. Not only will it help save you time if your current file gets corrupted it will also allow you to go back to an earlier version if you decide (or are demanded) to take your project in a totally different direction. I mentioned two ways to stay organized in this article (SharePoint & creating your own version extensions); are there any other methods or programs that have helped you stay organized with your file history? Leave a comment below if you would be willing to share! I look forward to reading your thoughts.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.