Saving a Backup to the same folder

Throwaway8843

New Member
Joined
Jun 29, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Look I am sure this question has been asked and I have found some close answers online but I am having an issue.

VBA Code:
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?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Good thought, but when I change that, I get the error message that it cant find the specified filepath
 
Upvote 0
What happens when you step through and hover over the path? Is the book saved already? Working here on 365
 
Upvote 0
it gets stuck on the "ActiveWorkbook.SaveCopyAs Filename:=backupFolder & Replace(ActiveWorkbook.Name, ".xlsm", "") & " " & formatDate & ".xlsm" section

VBA Code:
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.
 
Upvote 0
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
 
Upvote 0
What about:

VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
It is just to return the full name and path in the immediate window:

You may need to add it here:
 

Attachments

  • 1625009888531.png
    1625009888531.png
    74 KB · Views: 38
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top