Excel file backup with TimeStamp

[Anbuselvam]

Dear Excel Genius

I need a VBA code to AutoSave excel file with time stamp DD:MMM:YYYY_HH:MM: SS_FileName.

Whenever I opened the file it has to save on the OpenBackup Folder which is located in the same file path of the Excel file.

Whenever I Close the file (Save or Don't Save) it has to save on the ClosingBackup Folder which is located in the same file path of the Excel file.

Both open and closing files have to TimeStamp and the Password of "Password"

But the original file needs to save in its original location as per my Save or Don'tSave option selection with its original name.

Thanks in Advance!

 

[Anbuselvam]

Dear Bobsan42

Thanks a lot for your explanation in detail. I am trying to make the code workable for Mac OS Office 365. I keep update to you here if any updates.

In the meanwhile, if you can modify the code with ActiveWorkbook.Path & Application. path separator then it would be a very great help for me as there are problems in the file backup path only.

but the folders you created are "Closing Backup" and "Open Backup" (an extra space in the middle)

Code also I have given the space in the names, so it is not the issue as per my view.

I can't quite figure why the filenames start with "Backup\" - is the file placed in the Backup folder ?

It is not mandatory to start the file name with Backup. it can be "DD-MMM-YYYY HH-MM-SS AM/PM.FileName"

However, this line is a sort of a safety switch:

If InStr(1, .FullName, "backup\", vbTextCompare) > 0 Then Exit Sub

The code should exit if the file is started from a folder that contains "backup" - this is to avoid backing up the backuped files. Maybe this can be done in a more sofisticated way.

Really I did not understand what and how these lines take care of and modify.

About the online path issue: It seems the Office apps (and VBA) are not allowed to see directly the local file path if it is synced with OneDrive. I found several solutions for returning the local path rather than the online one. They work on a windows machine. I can't tell about a Mac.
Check these links:

This is why I moved my files and folders from OneDrive to Desktop. But still, it is not fixed.

Maybe you will need another solution for mac.

Expecting help from other Members who use the Mac OS Office 365 version.

 

[Anbuselvam]

Hi

The below code is working perfectly and save the files to the "Open Backup" and "Closing Backup" without a password.

For the password option If I use ThisWorkbook.SaveAs then the original file moved to open Backup while opening.
No changes saved in the original location.

If someone helps me out to add a password other than SaveCopyAs, it would be a great help for me.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Dim tPath As String
            tPath = ActiveWorkbook.Path & Application.PathSeparator & "Closing Backup/"
            ThisWorkbook.SaveCopyAs tPath & Format(Now, "dd-mmm-yyyy hh-mm-ss AM/PM") & " - " & ActiveWorkbook.Name
End Sub
Private Sub Workbook_Open()
        Dim tPath As String
            tPath = ActiveWorkbook.Path & Application.PathSeparator & "Open Backup/"
            ThisWorkbook.SaveCopyAs tPath & Format(Now, "dd-mmm-yyyy hh-mm-ss AM/PM") & " - " & ActiveWorkbook.Name
End Sub