VBA Code To Save As A New Version If File Already Exists

Excel Word PowerPoint 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 Function

VBA 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 Sub

VBA Code of Version Control For Microsoft Word

The only thing you need to change from the first Excel VBA code is 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 Sub

VBA 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 Sub

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

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris "Macro" Newman :)