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!

 

[bobsan42]

Place the following code in ThisWorkbook code module and read the comments:

Option Explicit

'# the code assumes that both backup folders exist

Const pwd = "" 'if you workbook has a password - put it between the quotes
Const fmt = "DDMMMYYYY_HHMMSS_" 'IMHO YYYYMMDD_HHMMSS_ would make more sense
Const bupwd = "Password"

Private Sub wbCopy(subfolder As String)
    On Error Resume Next
    With ThisWorkbook
        If InStr(1, .FullName, "backup\", vbTextCompare) > 0 Then Exit Sub
        .Password = bupwd
        .SaveCopyAs .Path & "\" & subfolder & "\" & Format(Now, fmt) & .Name
        .Password = pwd
    End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    wbCopy "ClosingBackup"
End Sub

Private Sub Workbook_Open()
    wbCopy "OpenBackup"
End Sub

 

[Anbuselvam]

Dear bobsan42

Thanks a lot for your valuable reply.

I have pasted the code in the "Thisworkbook" Module as below.

Option Explicit

'# the code assumes that both backup folders exist

Const pwd = "" 'if you workbook has a password - put it between the quotes
Const fmt = "DDMMMYYYY_HHMMSS_" 'IMHO YYYYMMDD_HHMMSS_ would make more sense
Const bupwd = "allowme"

Private Sub wbCopy(subfolder As String)
    On Error Resume Next
    With ThisWorkbook
        If InStr(1, .FullName, "backup\", vbTextCompare) > 0 Then Exit Sub
        .Password = bupwd
        .SaveCopyAs .Path & "\" & subfolder & "\" & Format(Now, fmt) & .Name
        .Password = pwd
    End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    wbCopy "Closing Backup"
End Sub

Private Sub Workbook_Open()
    wbCopy "Open Backup"
End Sub

I have opened and closed it several times and it's not saved to the specific folder.

Below the image is the folder and the excel file "Costing VBA" locations.

Note: I do not put any password for the excel file but I have a password for the VBA Project. I hope it is not related to the first line of your suggested code.

 

[bobsan42]

1. Check if your file path contains "backup\"
2. Comment the line "on error resume next" to see if an error occurs during code execution
3. Do you have permissions to write to this locations?
4. I assume that macros are allowed to run
5. Try to execute wbCopy "Closing Backup" manually from the immediate window one step at a time and debug
6. What is your system - mine returns thisworkbook.path without a back slash at the end.
7. You may add a breakpoint somewhere to see if the code runs at all. Are events enabled?

 

[Anbuselvam]

Thanks for your reply.

I have changed the "Backup" to "Costing" as I have the Costing folder instead of Backup

I have commented the line "On Error Resume Next" Then got the error of the below image

I do not know how to grant permission to access the folder as it is not asking while opening or closing the file.

Can you please use the file path "ActiveWorkbook.Path" & Open or Closing Folder?

My system and other systems also have the same folders in the same excel file path only.

So that it can be used by other users where they do not have "Backup" or "Costing" Folders

 

[bobsan42]

It seems you use the file directly from OneDrive / online. I assumed local file.
I will look into it a bit later.
Maybe someone will provide a valuable input 8n the meantime.

 

[Anbuselvam]

It is a local file only, But it is syncing with Onedrive. The file path is below FYI...

Users/Username/OneDrive/Aliens/Costing/Open Backup
Users/Username/OneDrive/Aliens/Costing/Closing Backup
Users/Username/OneDrive/Aliens/Costing/Costing VBA.xlsm

 

[bobsan42]

Yes but the path returns as https://...

 

[Anbuselvam]

I have created 3 folders called Backup, Open Backup and Closing Backup Folder on the desktop, to avoid the online path.
Then Inside the Backup folder, I added again Open Backup, Closing Backup folders and Costing VBA.xlsm

When I open the .xlsm file it creates an open backup on the desktop and two closing backups on the desktop.

It is not saving the backup files on the "Open Backup" and the "Closing backup" folders.

Please check the below image

The code is below.

Option Explicit

'# the code assumes that both backup folders exist

Const pwd = "" 'if you workbook has a password - put it between the quotes
Const fmt = "DDMMMYYYY_HHMMSS_" 'IMHO YYYYMMDD_HHMMSS_ would make more sense
Const bupwd = "Password"

Private Sub wbCopy(subfolder As String)
    On Error Resume Next
    With ThisWorkbook
        If InStr(1, .FullName, "backup\", vbTextCompare) > 0 Then Exit Sub
        .Password = bupwd
        .SaveCopyAs .Path & "\" & subfolder & "\" & Format(Now, fmt) & .Name
        .Password = pwd
    End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    wbCopy "ClosingBackup"
End Sub

Private Sub Workbook_Open()
    wbCopy "OpenBackup"
End Sub

 

[bobsan42]

The code works flawlessly on a windows system. But you are on a Mac AFAICT.
I cannot test my code on a mac. And I have not used one for too many years now. more than 20 maybe.

I don't remember the details of how the file system works - it seems to add bits of the path to the filename rather than recognising them as folder names.

• Maybe each back slash (\) must be replaced by a slash (/) (as it is a unix based system).
• If the file will be used on various platforms - a check must be implemented
• Maybe avoid Spaces in folder names JIC.

In the last code the close and open events call:

wbCopy "ClosingBackup"

and

wbCopy "OpenBackup"

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

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

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.

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:
Excel's fullname property with OneDrive
how to get physical path instead of URL (OneDrive)
Maybe you will need another solution for mac.