Saving a Backup to the same folder

[Throwaway8843]

Look I am sure this question has been asked and I have found some close answers online but I am having an issue.

Sub FileBackUp()

' Saves a backup copy of the excel document in the same folder with the current date and name of the file.

 Dim saveDate As Date
 Dim formatDate As String
 Dim backupFolder As String

    saveDate = Date
   
    formatDate = Format(saveDate, "DD - MM - YYYY")

    Application.DisplayAlerts = False
        backupFolder = ThisWorkbook.Path & "\"
        ActiveWorkbook.SaveCopyAs Filename:=backupFolder & Replace(ActiveWorkbook.Name, ".xlsm", "") & " " & formatDate & ".xlsm"
    Application.DisplayAlerts = True

    MsgBox "Backup Successfully In The Path " & backupFolder

End Sub

When I run this code it saves the backup in the personal workbooks filepath rather than the in the filepath of the current excel I am using. Is there any way to change that while keeping the file location dynamic?

 

[mrshl9898]

Try changing ThisWorkbook.Path to ActiveWorkbook.Path

 

[Throwaway8843]

Good thought, but when I change that, I get the error message that it cant find the specified filepath

 

[mrshl9898]

What happens when you step through and hover over the path? Is the book saved already? Working here on 365

 

[Throwaway8843]

it gets stuck on the "ActiveWorkbook.SaveCopyAs Filename:=backupFolder & Replace(ActiveWorkbook.Name, ".xlsm", "") & " " & formatDate & ".xlsm" section

Sub FileBackUp()

' Saves a backup copy of the excel document in the same folder with the current date and name of the file.

 Dim saveDate As Date
 Dim formatDate As String
 Dim backupFolder As String

    saveDate = Date
    
    formatDate = Format(saveDate, "DD - MM - YYYY")

    Application.DisplayAlerts = False
        backupFolder = ActiveWorkbook.Path & "\"
        ActiveWorkbook.SaveCopyAs Filename:=backupFolder & Replace(ActiveWorkbook.Name, ".xlsm", "") & " " & formatDate & ".xlsm"
    Application.DisplayAlerts = True

    MsgBox "Backup Successfully In The Path " & backupFolder

End Sub

There is a workbook saved as "VBA Template" in that folder (in this test case my desktop but this wont be the case usually). But there is not a workbook saved as VBA Template 6-29-2021 in the desktop currently. It was working saving to my personal folder before changing thiswookbook.path to activeworkbook.path, but that just wasnt the right folder.

 

[Throwaway8843]

Also on 365 here

 

[mrshl9898]

So you can see the file location it is saving it to, and there is no file named that in the folder and it won't save? The code is fine, i'd say one of those is not the case, unless formatDate is blank

 

[mrshl9898]

What about:

Sub FileBackUp()

' Saves a backup copy of the excel document in the same folder with the current date and name of the file.

 Dim saveDate As Date
 Dim formatDate As String
 Dim backupFolder As String

    saveDate = Date
   
    formatDate = Format(saveDate, "DD - MM - YYYY")

    Application.DisplayAlerts = False
        backupFolder = ActiveWorkbook.Path & "\"
        Debug.Print backupFolder & Replace(ActiveWorkbook.Name, ".xlsm", "") & " " & formatDate & ".xlsm"
    Application.DisplayAlerts = True

    MsgBox "Backup Successfully In The Path " & backupFolder

End Sub

 

[Throwaway8843]

So the error message I was receiving before your latest message was "Sorry we couldn't find https://company-my.sharepoint.com/personal/name_company_com/Template 29 - 06 - 2021.xlsm. Is it possible it was moved, renamed or deleted?"

When I run your more recent code no error message or msgbox appears, although I get the microsoft error noise in my headphones. Nothing is saved on my desktop from what I can tell and nothing appears in my immediate window either.

 

[mrshl9898]

It is just to return the full name and path in the immediate window:

You may need to add it here: