Solved: Create backup to another location

[ioncila]

Hi
Some time ago, I found this code in the web (dont know the author, sorry) to create backup files.

It works almost as I wish. I've changed it to create the ".bak" file when workbook open, to automatize the procedure whenever I open the file. However the backups are created in same location of the originals, and I want them stored in another folder, say i.e., "C:\Backups\". But I don´t know where in the code I put that piece.

Other thing, to automatically create .bak files when workbook is closing, sall I use WorkbookBeforeClose Method?

Here's the code

Private Sub Workbook_Open() 'Fazer backup
Dim awb As Workbook, BackupFileName As String, i As Integer, OK As Boolean
If TypeName(ActiveWorkbook) = "Nothing" Then Exit Sub
Set awb = ActiveWorkbook
If awb.Path = "" Then
Application.Dialogs(xlDialogSaveAs).Show
Else
BackupFileName = awb.FullName
i = 0
While InStr(i + 1, BackupFileName, ".") > 0
i = InStr(i + 1, BackupFileName, ".")
Wend
If i > 0 Then BackupFileName = Left(BackupFileName, i - 1)
BackupFileName = BackupFileName & ".bak"
OK = False
On Error GoTo NotAbleToSave
With awb
Application.StatusBar = "Saving this workbook..."
.Save
Application.StatusBar = "Saving this workbook backup..."
.SaveCopyAs BackupFileName
OK = True
End With
End If
NotAbleToSave:
Set awb = Nothing
Application.StatusBar = False
If Not OK Then
MsgBox "Backup Copy Not Saved!", vbExclamation, ThisWorkbook.Name
End If
End Sub

Thanks in advance for your precious help.
Ioncila

[lucas]

I don't understand why you are saving as a .bak file but this looks easier and gives you some options for filname in case you save the same workbook twice......

Option Explicit
Sub SvMe() 'Save filename as value of A1 plus the current date
Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax
'fName = Range("A1").Value
newFile = ActiveWorkbook.Name
' newFile = fName & " " & Format$(Date, "mm-dd-yyyy")
' Change directory to suit
ChDir _
"C:\Temp"
ActiveWorkbook.SaveCopyAs Filename:=newFile
' ActiveWorkbook.SaveCopyAs Filename:=newFile & ".xls"
End Sub

[ioncila]

Thank you for your help

Your solution is much better than mine.

A little change to automatize the copy procedure, I had do this (underline):

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Save filename as value of A1 plus the current date
Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax
'fName = Range("A1").Value
newFile = ActiveWorkbook.Name
' newFile = fName & " " & Format$(Date, "mm-dd-yyyy")
' Change directory to suit
ChDir _
"C:\Testes"
ActiveWorkbook.SaveCopyAs Filename:=newFile
' ActiveWorkbook.SaveCopyAs Filename:=newFile & ".xls"
End Sub

And that works as wish, but if I use the copy file, it gives an error in the bold line.
That is not quiet important because the copy file will be used in an emergency, but is there a way to prevent that?

Thanks

[lucas]

It works just fine for me. does the directory C:\Testes exist and is the original file saved so it actually has a name?

[ioncila]

It works just fine for me. does the directory C:\Testes exist and is the original file saved so it actually has a name?

I did the following:

As an experience, I saved original (Testbackup.xls) in desktop and created "C:\Testes to store the copy one.

The copy file has the same name of the original.

Like I said, copy procedure works fine, like I want. Then If I open the copy file, and stiil it's allright. But when I close it, that brings an error on that bold code line with msg "impossible to access to Testbackup.xls". I think the error appears baecause macro still exist in the copyfile and it can't find the path.
Like I said, that is not important because the important function is done (create a backup copy).

One small question:
shouldn't the copy file name appear with date, like your code means?

Thanks

[lucas]

The reason you get an error is because you are opening it in the directory that it needs to save to, ie C:\Testes and that named file is already in existance there.

If you move it out of that directory, it will run again.

That's why you need to add the date or somthing so the file name won't be the same each day.
Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Save filename as value of A1 plus the current date
Dim newFile As String, fName As String
newFile = ActiveWorkbook.Name
ChDir _
"C:\Temp"
ActiveWorkbook.SaveCopyAs Filename:=newFile & " " & Format$(Date, "mm-dd-yyyy") & ".xls"
End Sub

It might be better not to have it in the close event and just have a button to save it unless you are sure you will only be opening it once a day and want to save a copy.....

[ioncila]

That suits my needs

Thank you very much