VBA Backup Code for Excel that also uses Date Time

[JeffGrant]

Hi All,
does anybody have a vba routine that will add Date & Time to the back up file name, however, I would like something a little more than

ActiveWorkbook.SaveAs ("C:\MyDrive " & Format(Now(), "DD-MMM-YYYY hh mm AMPM") & ".xlsm")

I find the SAVE AS routine very annoying because of the change of active file when the SAVE AS is performed.

Ideally, I would like to:
Keep the same file name
Have the back up copy file name be Filename + Date & Time - so it is unique
But NOT change the active file.

Any ideas.

thanks

 

[dmt32]

Try using SaveCopyAs method

ActiveWorkbook.SaveCopyAs ("C:\MyDrive " & Format(Now(), "DD-MMM-YYYY hh mm AMPM") & ".xlsm")

Workbook.SaveCopyAs method (Excel)

Office VBA reference topic

docs.microsoft.com

Dave

 

[JeffGrant]

HI Dave,

Thanks for that suggestion.

I just tried changing to saving as .xls which is backward compatible to 2003.

Unfortunately, it still changed the active workbook.

 

[dmt32]

You can only use SaveCopyAs to save a copy of a file of same file format. SaveCopyAs method should not modify the open workbook in memory.

If your file is xlsm format there is no parameter in the method that allows you to change it to say xls format. To do this, you would need to write a custom SaveAs function.


Dave

 

[rjmdc]

you could try

Sub Auto_Save()
    Dim saveDate As Date
    Dim saveTime As Variant
    Dim formatTime As String
    Dim formatDate As String
    Dim backupFolder As String
    Dim FileExt As String
    Dim ThisFileName As String
          
    saveDate = Now
    FileExt = ".xlsx"
    
    formatDate = Format(saveDate, "MM-DD-YYYY hh.MM")
            
    Application.DisplayAlerts = False
    backupFolder = "C:\my drive\"
    
    ThisFileName = "name of file" & formatDate & FileExt
    
    ActiveWorkbook.SaveCopyAs FileName:=backupFolder & ThisFileName
    Application.DisplayAlerts = True
    
    MsgBox "Backup Successfully In The Path " & backupFolder
  
End If
End Sub

 

[JeffGrant]

you could try

Sub Auto_Save()
    Dim saveDate As Date
    Dim saveTime As Variant
    Dim formatTime As String
    Dim formatDate As String
    Dim backupFolder As String
    Dim FileExt As String
    Dim ThisFileName As String
         
    saveDate = Now
    FileExt = ".xlsx"
   
    formatDate = Format(saveDate, "MM-DD-YYYY hh.MM")
           
    Application.DisplayAlerts = False
    backupFolder = "C:\my drive\"
   
    ThisFileName = "name of file" & formatDate & FileExt
   
    ActiveWorkbook.SaveCopyAs FileName:=backupFolder & ThisFileName
    Application.DisplayAlerts = True
   
    MsgBox "Backup Successfully In The Path " & backupFolder
 
End If
End Sub

Thanks rjmdc, I'll give this ago.