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

 

[Skyybot]

FYI - My One Drive file locations can also be referenced as local file locations. If I open a file on my One Drive and print the

ThisWorkbook.Path

it shows as

"C:\Users\Me\OneDrive\Documents"

. How are you accessing One Drive?

 

[dpaton05]

I am accessing it from my work computer. The start of the file name is "C:\Users\...."

 

[dpaton05]

I can't post the rest due to privacy reasons.

I have found this code that I am trying to use.

Sub Save_Spreadsheet()
    Dim CurrentDate As String
    Dim FilePath As String
    Dim FileName As String
   
    CurrentDate = Format(Date, "yyyy_mm_dd")
   
    ' change the file directory and file name as needed.
    FilePath = "C:\Users\....."
    FileName = "MyWorkbook_"

    ActiveWorkbook.SaveAs FilePath & FileName & CurrentDate & ".xlsx"    
    MsgBox("File create succesfully")
   

End Sub

How do I get the current date to appear with the time as well?

 

[Skyybot]

I wonder if swapping out all of the ThisWorkbook with ActiveWorkbook would work?

 

[Skyybot]

Sub Save_Spreadsheet()
    Dim CurrentDate As String
    Dim FilePath As String
    Dim FileName As String
   
    CurrentDate = Format(Date, "yyyy_mm_dd")
   
    ' change the file directory and file name as needed.
    FilePath = "C:\Users\....."
    FileName = "MyWorkbook_"

    ActiveWorkbook.SaveAs FilePath & FileName & CurrentDate & ".xlsx"   , xlOpenXMLWorkbook
    MsgBox("File create succesfully")
   

End Sub

 

[dpaton05]

This is my code:

Sub Save_Spreadsheet()
    Dim CurrentDate As String
    Dim FilePath As String
    Dim FileName As String
    
    CurrentDate = Format(Date, "yyyy_mm_dd")
    
    ' change the file directory and file name as needed.
    FilePath = "C:\Users\...."
    FileName = "MyWorkbook_"
    
    ActiveWorkbook.SaveAs FilePath & FileName & CurrentDate & ".xlsm"

End Sub

There is a location after C:\Users\ but I have had to remove it due to privacy.

What is the code to get a spreadsheet saved in "DD.MM.YY - HH.MM"?

 

[Skyybot]

CurDateTime = Date & "-" & Hour(Now()) & ":" & Minute(Now())

 

[dpaton05]

Thanks for that. Where do I put that code?

 

[dpaton05]

I tried to put it after current date but it gave me another error. I have attached it.

Sub Save_Spreadsheet()
    Dim CurrentDate As String
    Dim FilePath As String
    Dim FileName As String
    
    CurrentDate = Date & "-" & Hour(Now()) & ":" & Minute(Now())
    'Format(Date, "yyyy_mm_dd")
    
    ' change the file directory and file name as needed.
    FilePath = "C:\Users\SpiritAbility\OneDrive - SpiritAbility\Reception Folder\Qualifications\Backups\"
    FileName = "MyWorkbook_"
    
    ActiveWorkbook.SaveAs FilePath & FileName & CurrentDate & ".xlsm"

End Sub

 

[Skyybot]

That's because you can't have a colon in a file name.

CurDateTime = Date & "-" & Hour(Now()) & "-" & Minute(Now())