Automate Saving New File Version

How to automate the process of saving an existing file as a new version so that it is not overwritten.
Watch the training tutorial video below:

Here’s the complete VBA code to save an existing file as a new version:
Option Explicit

Sub SaveFileAsNewVersion()
Dim myFolderPath As String
Dim myPath As String
Dim Savename As String
Dim myVersion As String
Dim saveext As String
Dim Saved As Boolean
Dim i As Long

Dim Teststr As String
Dim myFileName As String
Dim myarray As Variant

Teststr = “”
Saved = False

i = 1
‘version marker
myVersion = “_ver”

‘Get info about our existing file
On Error GoTo NotYetSaved
myPath = ActiveWorkbook.FullName
myFileName = Mid(myPath, InStrRev(myPath, “\”) + 1, InStrRev(myPath, “.”) – InStrRev(myPath, “\”) – 1)
myFolderPath = Left(myPath, InStrRev(myPath, “\”))
saveext = “.” & Right(myPath, Len(myPath) – InStrRev(myPath, “.”))

On Error GoTo 0

If InStr(1, myFileName, myVersion) >= 1 Then
myarray = Split(myFileName, myVersion)
Savename = myarray(0)
Else
Savename = myFileName
End If

If FileExist(myFolderPath & Savename & saveext) = False Then
ActiveWorkbook.SaveAs myFolderPath & Savename & saveext
Exit Sub
End If

Do While Saved = False
If FileExist(myFolderPath & Savename & myVersion & i & saveext) = False Then
ActiveWorkbook.SaveAs myFolderPath & Savename & myVersion & i & saveext
Saved = True
Else
i = i + 1
End If
Loop

Exit Sub

NotYetSaved:
MsgBox “This file has never been saved.” & _
“Therefore cannot save as a new version!”, vbCritical, “Not saved!”

End Sub

Function FileExist(FilePath As String) As Boolean
‘Reference: http://www.rondebruin.nl/win/s9/win003.htm
Dim Teststr As String
On Error Resume Next
Teststr = Dir(FilePath)
On Error GoTo 0
If Teststr = “” Then
FileExist = False
Else
FileExist = True
End If

End Function

Further Reading:
VBA Code to save as a new version if file already exists

Test if Folder, File or Sheet exists or File is open

SPLIT Function

InStrRev Function

MID Function

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

One thought on “Automate Saving New File Version”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.