February 10, 2020

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

One thought on “Automate Saving New File Version

  1. What is the macro for invoice automatically save as PDF in Ms exel 2007?

Comments are closed.