Rename a file with different versions each time saved

gtd526

gtd526

Well-known Member
Joined
Jul 30, 2013
Messages
682
Office Version
  1. 2019
Platform
  1. Windows
Hello,
I have a workbook that I want to save as (in the same folder) version1, then version2, then version3, etc. depending on how many times I save it. I want to keep the original file as is.
I want the version to increase by 1 each time its saved. So in the same folder I might see FileName (version1), FileName (version2), etc. The extension will be .xlsm

Thank you
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
gtd526 said:
Hello,
I have a workbook that I want to save as (in the same folder) version1, then version2, then version3, etc. depending on how many times I save it. I want to keep the original file as is.
I want the version to increase by 1 each time its saved. So in the same folder I might see FileName (version1), FileName (version2), etc. The extension will be .xlsm

Thank you
Click to expand...
Place this code in the ThisWorkbook Code Module.

It saves the workbook with a new version number each time the workbook is saved.

It also saves the workbook under it's original name at the same time. Did you want this to happen?

VBA Code: 
Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim fso As Object

  Set fso = CreateObject("Scripting.FileSystemObject")
  
  With ThisWorkbook
  
    ActiveWorkbook.SaveCopyAs .Path & "\" & fso.GetBaseName(ThisWorkbook.Name) & _
      " (Version " & fncGetNextVersionNumber & ").xlsm"
  
  End With
  
  Set fso = Nothing
      
End Sub

Public Function fncGetNextVersionNumber() As Long

    ' Set a reference to Microsoft Scripting Runtime by using
    ' Tools > References in the Visual Basic Editor (Alt+F11).
    
    ' Declare the variables.
    Dim objFSO As FileSystemObject
    Dim objFolder As Folder
    Dim objFile As File
    Dim lngVersion As Long
    Dim varVersion As Variant
    
    ' Create an instance of the FileSystemObject.
    Set objFSO = CreateObject("Scripting.FileSystemObject")
        
    ' Get the folder.
    Set objFolder = objFSO.GetFolder(ThisWorkbook.Path & "\")
        
    ' If the folder does not contain files, exit the function.
    ' The version number is set to 1.
    If objFolder.Files.Count = 0 Then
      fncGetNextVersionNumber = 1
      Exit Function
    End If
        
    ' Loop through each file in the folder.
    For Each objFile In objFolder.Files
          
      ' If the file is a saved workbook for this workbook.
      If objFSO.GetBaseName(objFile) Like "*" & objFSO.GetBaseName(ThisWorkbook.Name) _
        & " (Version" & "*" Then
        
        ' Extract the version number from the filename.
        varVersion = Replace(Replace(objFSO.GetBaseName(objFile), _
          objFSO.GetBaseName(ThisWorkbook.Name) & " (Version ", "", 1), ")", "", 1)
        
        ' Check to see if the version exceeds the previous file version.
        ' If so then set the latest version.
        If IsNumeric(varVersion) And (varVersion > lngVersion) Then
          lngVersion = varVersion
        End If
        
      End If
      
    Next objFile
    
    Set objFolder = Nothing
    
    Set objFSO = Nothing
    
    fncGetNextVersionNumber = lngVersion + 1
            
End Function
 
Upvote 0
Solution
HighAndWilder said:
Place this code in the ThisWorkbook Code Module.

It saves the workbook with a new version number each time the workbook is saved.

It also saves the workbook under it's original name at the same time. Did you want this to happen?

VBA Code: 
Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim fso As Object

  Set fso = CreateObject("Scripting.FileSystemObject")
 
  With ThisWorkbook
 
    ActiveWorkbook.SaveCopyAs .Path & "\" & fso.GetBaseName(ThisWorkbook.Name) & _
      " (Version " & fncGetNextVersionNumber & ").xlsm"
 
  End With
 
  Set fso = Nothing
     
End Sub

Public Function fncGetNextVersionNumber() As Long

    ' Set a reference to Microsoft Scripting Runtime by using
    ' Tools > References in the Visual Basic Editor (Alt+F11).
   
    ' Declare the variables.
    Dim objFSO As FileSystemObject
    Dim objFolder As Folder
    Dim objFile As File
    Dim lngVersion As Long
    Dim varVersion As Variant
   
    ' Create an instance of the FileSystemObject.
    Set objFSO = CreateObject("Scripting.FileSystemObject")
       
    ' Get the folder.
    Set objFolder = objFSO.GetFolder(ThisWorkbook.Path & "\")
       
    ' If the folder does not contain files, exit the function.
    ' The version number is set to 1.
    If objFolder.Files.Count = 0 Then
      fncGetNextVersionNumber = 1
      Exit Function
    End If
       
    ' Loop through each file in the folder.
    For Each objFile In objFolder.Files
         
      ' If the file is a saved workbook for this workbook.
      If objFSO.GetBaseName(objFile) Like "*" & objFSO.GetBaseName(ThisWorkbook.Name) _
        & " (Version" & "*" Then
       
        ' Extract the version number from the filename.
        varVersion = Replace(Replace(objFSO.GetBaseName(objFile), _
          objFSO.GetBaseName(ThisWorkbook.Name) & " (Version ", "", 1), ")", "", 1)
       
        ' Check to see if the version exceeds the previous file version.
        ' If so then set the latest version.
        If IsNumeric(varVersion) And (varVersion > lngVersion) Then
          lngVersion = varVersion
        End If
       
      End If
     
    Next objFile
   
    Set objFolder = Nothing
   
    Set objFSO = Nothing
   
    fncGetNextVersionNumber = lngVersion + 1
           
End Function
Click to expand...
Thank you for the code. I have a macro that saves appropriately but only works if I'm using NBA (version 1).xlsm as the open file. "NBA" is the name of the file.
How can I alter the macro to look for NBA.xlsm as the open file, then SAVEAS version1, 2, etc in the same folder as the original.

VBA Code: 
Sub My_SaveAs()
'updates Version 1,2,3 etc ONLY if you USE Version 1 or 2, etc
'it will add 1 to the Version your using 1 to 2, 2 to 3, 3 to 4,etc

    Dim n As Long
    Dim wbName As String, wbPath As String, ver As String
    
    Application.DisplayAlerts = False  'to rid of override the file question
    
        wbName = Replace(ActiveWorkbook.Name, ".xlsm", "")
        wbPath = ActiveWorkbook.Path
        If wbName Like "*(version*" Then
            n = InStr(wbName, "(version")
            ver = Right(wbName, Len(wbName) - n + 1)
            wbName = Left(wbName, Len(wbName) - n - 7)
            If IsNumeric(Left(Right(ver, 2), 1)) Then
                n = Left(Right(ver, 2), 1)
            Else
                n = 0
            End If
        End If
        ActiveWorkbook.SaveAs Filename:=wbPath & "\" & wbName & " (version " & n + 1 & ").xlsm", FileFormat:=52
    
    Application.DisplayAlerts = True

End Sub
 
Upvote 0
gtd526 said:
Thank you for the code. I have a macro that saves appropriately but only works if I'm using NBA (version 1).xlsm as the open file. "NBA" is the name of the file.
How can I alter the macro to look for NBA.xlsm as the open file, then SAVEAS version1, 2, etc in the same folder as the original.

VBA Code: 
Sub My_SaveAs()
'updates Version 1,2,3 etc ONLY if you USE Version 1 or 2, etc
'it will add 1 to the Version your using 1 to 2, 2 to 3, 3 to 4,etc

    Dim n As Long
    Dim wbName As String, wbPath As String, ver As String
   
    Application.DisplayAlerts = False  'to rid of override the file question
   
        wbName = Replace(ActiveWorkbook.Name, ".xlsm", "")
        wbPath = ActiveWorkbook.Path
        If wbName Like "*(version*" Then
            n = InStr(wbName, "(version")
            ver = Right(wbName, Len(wbName) - n + 1)
            wbName = Left(wbName, Len(wbName) - n - 7)
            If IsNumeric(Left(Right(ver, 2), 1)) Then
                n = Left(Right(ver, 2), 1)
            Else
                n = 0
            End If
        End If
        ActiveWorkbook.SaveAs Filename:=wbPath & "\" & wbName & " (version " & n + 1 & ").xlsm", FileFormat:=52
   
    Application.DisplayAlerts = True

End Sub
Click to expand...
My code will work whatever the file name is.

It assumes that the open file does not have (Version in its name as it is not a saved version as you wanted in your original post.

Have you tried my code?
 
Upvote 0
HighAndWilder said:
My code will work whatever the file name is.

It assumes that the open file does not have (Version in its name as it is not a saved version as you wanted in your original post.

Have you tried my code?
Click to expand...
I had the other macro before I tried yours.
I've completed your macro, and it works well.
Can we use a non-personal macro so I can create versions at a certain time, not every time I save the file?
I created a 'Macro' ribbon with different buttons to activate specific macros when the time is appropriate.
I would like to add your macro to that Ribbon.
 
Upvote 0
gtd526 said:
Thank you for the code. I have a macro that saves appropriately but only works if I'm using NBA (version 1).xlsm as the open file. "NBA" is the name of the file.
How can I alter the macro to look for NBA.xlsm as the open file, then SAVEAS version1, 2, etc in the same folder as the original.

VBA Code: 
Sub My_SaveAs()
'updates Version 1,2,3 etc ONLY if you USE Version 1 or 2, etc
'it will add 1 to the Version your using 1 to 2, 2 to 3, 3 to 4,etc

    Dim n As Long
    Dim wbName As String, wbPath As String, ver As String
  
    Application.DisplayAlerts = False  'to rid of override the file question
  
        wbName = Replace(ActiveWorkbook.Name, ".xlsm", "")
        wbPath = ActiveWorkbook.Path
        If wbName Like "*(version*" Then
            n = InStr(wbName, "(version")
            ver = Right(wbName, Len(wbName) - n + 1)
            wbName = Left(wbName, Len(wbName) - n - 7)
            If IsNumeric(Left(Right(ver, 2), 1)) Then
                n = Left(Right(ver, 2), 1)
            Else
                n = 0
            End If
        End If
        ActiveWorkbook.SaveAs Filename:=wbPath & "\" & wbName & " (version " & n + 1 & ").xlsm", FileFormat:=52
  
    Application.DisplayAlerts = True

End Sub
Click to expand...
My code will work whatever the file name is.

It assumes that the open file does not have (Version in its name as it is not a saved version as you wanted in your original post.

Have you tried my code?
gtd526 said:
I had the other macro before I tried yours.
I've completed your macro, and it works well.
Can we use a non-personal macro so I can create versions at a certain time, not every time I save the file?
I created a 'Macro' ribbon with different buttons to activate specific macros when the time is appropriate.
I would like to add your macro to that Ribbon.
Click to expand...

gtd526 said:
I had the other macro before I tried yours.
I've completed your macro, and it works well.
Can we use a non-personal macro so I can create versions at a certain time, not every time I save the file?
I created a 'Macro' ribbon with different buttons to activate specific macros when the time is appropriate.
I would like to add your macro to that Ribbon.
Click to expand...
Call this code from the ribbon button.

Move the fncGetNextVersionNumber function to a main code module from the workbook code module.

VBA Code: 
Public Sub subAdHocSaveCopyAs()
Dim fso As Object

  Set fso = CreateObject("Scripting.FileSystemObject")
  
  With ThisWorkbook
  
    ActiveWorkbook.SaveCopyAs .Path & "\" & fso.GetBaseName(ThisWorkbook.Name) & _
      " (Version " & fncGetNextVersionNumber & ").xlsm"
  
  End With
  
  Set fso = Nothing
      
End Sub
 
Upvote 0
HighAndWilder said:
My code will work whatever the file name is.

It assumes that the open file does not have (Version in its name as it is not a saved version as you wanted in your original post.

Have you tried my code?



Call this code from the ribbon button.

Move the fncGetNextVersionNumber function to a main code module from the workbook code module.

VBA Code: 
Public Sub subAdHocSaveCopyAs()
Dim fso As Object

  Set fso = CreateObject("Scripting.FileSystemObject")
 
  With ThisWorkbook
 
    ActiveWorkbook.SaveCopyAs .Path & "\" & fso.GetBaseName(ThisWorkbook.Name) & _
      " (Version " & fncGetNextVersionNumber & ").xlsm"
 
  End With
 
  Set fso = Nothing
     
End Sub
Click to expand...
Done. Thank you.
 
Upvote 0
HighAndWilder said:
My code will work whatever the file name is.

It assumes that the open file does not have (Version in its name as it is not a saved version as you wanted in your original post.

Have you tried my code?



Call this code from the ribbon button.

Move the fncGetNextVersionNumber function to a main code module from the workbook code module.

VBA Code: 
Public Sub subAdHocSaveCopyAs()
Dim fso As Object

  Set fso = CreateObject("Scripting.FileSystemObject")
 
  With ThisWorkbook
 
    ActiveWorkbook.SaveCopyAs .Path & "\" & fso.GetBaseName(ThisWorkbook.Name) & _
      " (Version " & fncGetNextVersionNumber & ").xlsm"
 
  End With
 
  Set fso = Nothing
     
End Sub
Click to expand...
Your macro is working great with my original file (NBA). There will be other files I wish to save as version 1, 2, etc.
So I copied the Public Sub subAdHocSaveCopyAs() macro to another workbook in the 'Workbook Code Module', and it's saving the version, but with no version #. ie filename(version ) is how its displayed in the given folder.
I placed the function routine in the Personal macro section. It works fine with my original (NBA), but no version # with my 2nd file.
How can we alter the code so it will save different versions regardless of the file were saving.
 
Upvote 0
gtd526 said:
Your macro is working great with my original file (NBA). There will be other files I wish to save as version 1, 2, etc.
So I copied the Public Sub subAdHocSaveCopyAs() macro to another workbook in the 'Workbook Code Module', and it's saving the version, but with no version #. ie filename(version ) is how its displayed in the given folder.
I placed the function routine in the Personal macro section. It works fine with my original (NBA), but no version # with my 2nd file.
How can we alter the code so it will save different versions regardless of the file were saving.
Click to expand...
Place the fncGetNextVersionNumber function in the same Workbook Code Module as the subAdHocSaveCopyAs procedure and let me know.

This is how I developed it and it works fine for me.
 
Upvote 0
HighAndWilder said:
Place the fncGetNextVersionNumber function in the same Workbook Code Module as the subAdHocSaveCopyAs procedure and let me know.

This is how I developed it and it works fine for me.
Click to expand...
I've add both GetNext function and AdHoc procedure to the Workbook Code Module in both testing files.
The first (NBA) works fine. But in the second, I receive an error: Compile error, and this line is highlighted-Dim objFSO As FileSystemObject, user defined type not defined
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VB search folder for older version of a file and move it to archive
Replies
7
Views
197
Davefromleeds
D
G
  • Question Question
remove columns from multiple Excel-files in a folder
Replies
5
Views
192
gjvv
G
O
  • Question Question
File name does not recalculate
Replies
5
Views
48
ozbeachbum
O
J
  • Solved
Macro runs more slowly each time it is run
2 3 4
Replies
34
Views
862
Jehannum_2000
J
VinceF
  • Question Question
VBA For Screenshot...With A Twist
2
Replies
14
Views
397
jolivanes
jolivanes

Forum statistics

Threads
1,223,577
Messages
6,173,164
Members
452,504
Latest member
frankkeith2233

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top