Macro to create a file backup

[bearcub]

I would like to create a macro where the file opens and the user is prompted (via a message box?) to save the file to a specific file location. The file is saved and a date and time stamp is added to the file name. Is this possible?

I would to make sure that every time this file is opened a backup is made automatically with the date and time it was saved.

Thank you for your help in advance,

Michael

 

[alansidman]

Maybe something like this:

Option Explicit


Sub SaveBkUp()
    Dim path As String
    Dim filename As String
    Dim d As Date
    d = Format(Date, "mm-dd-yy")
    path = InputBox("What path would you like to save the file to?")
    filename = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 11)
    ThisWorkbook.SaveAs path & "\" & filename & Format(d, "mmddyy") & ".xlsm"


End Sub

this assumes that the date is already included in the file name as mmddyy format.

 

[bearcub]

Thank you for the code. The date is being added to the filename. Does this make a difference to the code?

 

[bearcub]

I do have a question. Is there a way to open the save as dialog box so the user can specific where to save the file? the way the file is saved now the user would have to know the file path a head of time before they save it. I would like the user to be able to choose the file location (like when you use the save as option).

Thank you for your help in advance

 

[alansidman]

See if this works for you?

Option Explicit

Sub SaveBkUp()
Dim fldr As FileDialog
Dim path As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    '.InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    path = .SelectedItems(1)
    
End With
NextCode:
Dim filename As String
Dim d As Date
d = Format(Date, "mm-dd-yy")
filename = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 11)
ThisWorkbook.SaveAs path & "\" & filename & Format(d, "mmddyy") & ".xlsm"
End Sub

 

[bearcub]

thank you alan, the code seems to crash on the thisworkbook.saves line - it stops right there. the dialog comes up but it is only saving to a folder not a location. Nothing is being saved, sorry. And, the filename is not in the name box when the dialog box opens.

 

[alansidman]

Interesting that it crashed for you. I tested several times and it worked for me.

it is only saving to a folder not a location.

This I don't understand. What is a folder if not a location. Please clarify.

As to the file name. Why would you want the file name if you already have the file open with a name and are looking to save it at a selected location with a new date amended name. Perhaps I am not understanding your business issue. From the onset, you asked to save a named file (assumed to be open) with a new name. I am now confused. Please clarify in detail what you want specifically so we don't run in circles.

 

[bearcub]

Hi Alan,

When I run the macro the save as dialog box opens but it just points to a folder. It doesn't allow me to navigate to part file so I can copy the name of the file. For some reason, the code seems to crash on the last line of code - ThisWorkbook.SaveAs path & "" & filename & Format(d, "mmddyy") & ".xlsm". Nothing seems to get saved. Am I doing something wrong?

 

[bearcub]

I'm sorry, I must have been smoking something,

Now, the problem is, how do I get back to the file it was saved from. I would like the back up file to close and the original file I was saving is the one I see.

I apologize for the last couple of emails, the code you sent me works fine but it doesn't quit do what I need it to do.

 

[alansidman]

but it doesn't quit do what I need it to do.

I'm not really good at mind reading. You need to tell us and be specific what you want to do. I am unsure as you seem to have mentioned several things. Why not take a deep breath and explain to us in simple language as if we were standing in line to get a coffee what you want to accomplish. Step by Step. From start to finish so we don't miss anything.