Automatic Backup

[dlafko1]

I have an excel sheet that i want to store a backup anytime someone closes it. I have used this code but every time i hit save it puts it in the folder 2020 Internal External Postings & Database instead of going one more step and putting it the test folder. Which i will rename to back up. Not a a coding person so any help would be appreciated. This is also in a shared (Legacy ) Workbook.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Saves the current file to a backup folder and the default folder
'Note that any backup is overwritten
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveCopyAs Filename:="W:\Job Posting Spreadsheet - Excel\2020 Internal External  Postings & Database\test"
            ActiveWorkbook.Name
    ActiveWorkbook.Save
    Application.DisplayAlerts = True
End Sub

 

[Joe4]

I think we need to temporarily comment out the lines of code that are suppressing the alerts, as there could be important error messages that might be being suppressed.
There is no way that the code should be saving the file to the wrong folder - it can only save it to the folder we tell it to.

I tested it out, and confirmed that it should work. I even added a Message Box that returns a message that confirms where it saves the backup and what it named it.
So try this and see what it tells you:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Saves the current file to a backup folder and the default folder
'Note that any backup is overwritten

    Application.EnableEvents = False
    'Application.DisplayAlerts = False
    Dim myFileName As String
    myFileName = "W:\Job Posting Spreadsheet - Excel\2020 Internal External  Postings & Database\Backup\" & ActiveWorkbook.Name
    ActiveWorkbook.SaveCopyAs Filename:=myFileName
    MsgBox "Backup successfully saved to: " & vbCrLf & myFileName
    'Application.DisplayAlerts = True
    Application.EnableEvents = True

End Sub

 

[dlafko1]

Must be something with that folder.

I got it to work in another spread sheet so I will call IT see if they know why.

Thanks for the help

 

[Joe4]

What happened when you tried the code I posted above?

 

[dlafko1]

I got it.. I had made a module for it.. But when i removed the module and just put the code in the workbook vba it works great. Follow up to this then. Is there a way for it to be called backup or does it not matter cause the file is overwritten each time?

 

[Joe4]

I got it.. I had made a module for it.. But when i removed the module and just put the code in the workbook vba it works great

Yes, this is event procedure code that only runs automatically when placed in the "ThisWorkbook" module.
If you create your own Module and place it in there, it will not run automatically.

Is there a way for it to be called backup or does it not matter cause the file is overwritten each time?

You can name it whatever you like.
Do you want it called something else?
Do you want it overwritten each time?
Do you want a separate copy, maybe with a date stamp each time?

 

[dlafko1]

Yes, this is event procedure code that only runs automatically when placed in the "ThisWorkbook" module.
If you create your own Module and place it in there, it will not run automatically.


You can name it whatever you like.
Do you want it called something else? Just to add the name backup behind the title if possible
Do you want it overwritten each time? Yes no need to keep previous backups
Do you want a separate copy, maybe with a date stamp each time? NO the seperate copy that this code produces is great.

Thanks

 

[Joe4]

One simple way would be to change the line in my code that sets the file name like this:

myFileName = "W:\Job Posting Spreadsheet - Excel\2020 Internal External  Postings & Database\Backup\backup_" & ActiveWorkbook.Name

The only thing there is it puts the word "backup_" before the file name.
We could put it behind it, it is just a bit more complex, because then we have to parse the file name from the file extension, which is a few more steps.
It complicates matters, but can be done, if you really want to.

 

[dlafko1]

Let me try this but i think your solution is perfect.. Does not really matter where it says backup to me
Thanks for all the help

 

[Joe4]

You are welcome.

 

[dlafko1]

Thanks that worked great. Followup question. I use the legacy shared workbook, because we dont have the ability to use one drive. When i save it another user has also made changes it seems to take a few times to get it to save right. Can the code activate after all the built in Microsoft stuff is done so it imports the other users changes first then saves it complete? Maybe not and might just be something we have to work around.