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]

You need a backslash after the "test" folder name, i.e.
ActiveWorkbook.SaveCopyAs Filename:="W:\Job Posting Spreadsheet - Excel\2020 Internal External Postings & Database\test\"

 

[dlafko1]

Thanks added that but still not saving in there

'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\Backup\"
            ActiveWorkbook.Name
    ActiveWorkbook.Save
    Application.DisplayAlerts = True
End Sub

You need a backslash after the "test" folder name, i.e.
ActiveWorkbook.SaveCopyAs Filename:="W:\Job Posting Spreadsheet - Excel\2020 Internal External Postings & Database\test\"

 

[dlafko1]

Does it matter that the file location for the non back up file is in a different location then where i want to store the backup?

 

[Joe4]

Note that I think you need an ampersand between the end of the filepath and the filename, i..e
ActiveWorkbook.SaveCopyAs Filename:="W:\Job Posting Spreadsheet - Excel\2020 Internal External Postings & Database\Backup\" & ActiveWorkbook.Name

 

[dlafko1]

ok now I am getting this error

 

[Joe4]

Why do you have:
ActiveWorkbook.Save
inside of a BeforeSave event procedure?

I think that could throw you into an infinite loop.

Try this:

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
    ActiveWorkbook.SaveCopyAs Filename:="W:\Job Posting Spreadsheet - Excel\2020 Internal External  Postings & Database\Backup\" & ActiveWorkbook.Name
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub

 

[dlafko1]

Not sure what i am doing wrong but it still only saves in the main folder. This is in a shared drive idk if that matters.

 

[Joe4]

So, are you saying that it is NOT saving a copy to the "
"W:\Job Posting Spreadsheet - Excel\2020 Internal External Postings & Database\Backup\"
folder?

Do you have right to save it to that folder?

What happens if you manually try to browse and save a copy to that folder?
Are you able to, or do you get some sort of error message?

 

[dlafko1]

I can save to that folder and dropped and dragged the original in and changed the name to backup so no problem there. Just when I hit the save button it saves 2 copies the original and then names the other one back up but puts them both in the save folder.