Backup creation upon open

[dpaton05]

I want to create a backup of a file once I open it.

• The backup needs to be done the first time the file is opened each week, but only after a Wednesday, so Wednesday, Thursday or Friday.
• I only need it once per week and for the backup, just make a copy of the file.
• The location of the backup files need to be stored in a backup folder that is located in the current directory of the spreadsheet. It then needs to be separated by folders of year and month.
• The file name for the backup file needs to be "the name of the current file - the date the backup is run", for instance, "Sample_file - dd.mm.yy"

Thanks

 

[MARK858]

CurrentDate = Format(Now, "yyyy-mm-dd hhmm")

or

CurrentDate = Format(Now, "yyyy-mm-dd hh.mm")

 

[dpaton05]

Thank you Mark, that is perfect!!

For anyone who is reading this thread looking for an answer, here is my final, working code:

Sub Save_Spreadsheet()
    Dim CurrentDate As String
    Dim FilePath As String
    Dim FileName As String
    
    
    CurrentDate = Format(Now, "dd.mm.yy hh.mm AM/PM")
    

    FilePath = "C:\Users\....\OneDrive - .....\Reception Folder\Qualifications\Backups\"
    FileName = "Quals Expired and soon to expire backup - "
    
    ActiveWorkbook.SaveAs FilePath & FileName & CurrentDate & ".xlsm"

End Sub

The .... in the code are just blanking out the organisation name for privacy reasons.


The above code is stored in a module and ran when the workbook opens.

 

[dpaton05]

I just thought of something it needs to do Mark. I need it to check if the backup exists before copying and only copy if the backup doesn't exist.

 

[MARK858]

Try the syntax below

    Dim FilePath As String, FileExists As String
    FilePath = "yourfull_path_and_name_inc_extension"

    FileExists = Dir(FilePath)

    If FileExists = "" Then
        MsgBox "File doesnt exist"
    Else
        MsgBox "File exists"
    End If

 

[dpaton05]

Thanks Mark, how would I update my code to add what you suggest? There is also another question below the code.

Sub Save_Spreadsheet()
    Dim CurrentDate As String
    Dim FilePath As String
    Dim FileName As String
    
    
    CurrentDate = Format(Now, "dd.mm.yy hh.mm AM/PM")
    

    FilePath = "C:\Users\...\OneDrive - ...\Reception Folder\Qualifications\Backups\"
    FileName = "Quals Expired and soon to expire - Backup - "
    
    ActiveWorkbook.SaveAs FilePath & FileName & CurrentDate & ".xlsm"

End Sub

In addition to this issue, the code saves the backup file with the backup name but then you have the backup file open and any changes are made to the backup file instead of the working file. I need to save a copy of it in the background but then to keep working on the original file.