auto save copy as a backup upon opening workbook

[rjmdc]

the workbook contains many sheets and queries and code
somehow things go wrong and there is no backup
is there a way to create an auto backup upon opening the workbook and save in a specific backup folder
can the code give a message box do you want to backup? if vbyes then backup

 

[Logit]

Option Explicit

Sub workbook_open()

Dim FileName As String
Dim FileExt As String

FileName = Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)
FileExt = Right(ActiveWorkbook.Name, (Len(ActiveWorkbook.Name) + 1 - InStr(ActiveWorkbook.Name, ".")))

    If MsgBox("Would you like a back up of the file?", vbInformation + vbYesNo) = vbYes Then
        ActiveWorkbook.SaveCopyAs "C:\ ... your path goes here ... " & FileName & " - " & Format(Date, "dd.mmm.yyyy") & FileExt
    End If


End Sub

 

[dmt32]

Have you tried setting Always Create BackUp option when you saved the file?

1. Display the Save As dialog box.
2. Click the Tools option in the lower-right corner (next to Save button)
3. Click General Options. - Excel should display the General Options dialog box.
4. Check, Create Backup.
5. Click OK.
6. Continue saving the workbook.

see if this helps

Dave

 

[rjmdc]

hi
i got this far
how do i correct this code to save in a different folder?

Sub Auto_Save()
Dim saveDate As Date
Dim saveTime As Variant
Dim formatTime As String
Dim formatDate As String
Dim backupFolder As String

saveDate = Date
saveTime = Time

formatTime = Format(saveTime, "hh.MM.ss")
formatDate = Format(saveDate, "DD - MM - YYYY")

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

MsgBox "Backup Successfully In The Path " & backupFolder
End Sub

 

[rjmdc]

i am trying your code
it doesnt save into the specified folder
"M:\all\FI Coordinators - RBS & SRW\Backups"
it saves into
"M:\all\FI Coordinators - RBS & SRW"
and names the file backups

 

[dmt32]

i am trying your code
it doesnt save into the specified folder
"M:\all\FI Coordinators - RBS & SRW\Backups"
it saves into
"M:\all\FI Coordinators - RBS & SRW"
and names the file backups

Selecting the option I outlined is a built-in feature of Excel - the backup copy is saved in the same folder as the original File.
If you want to specify another folder then probably will need to use a macro to do this.

Dave

 

[Logit]

.
Try adding an "\" at the end of the path : "M:\all\FI Coordinators - RBS & SRW\Backups\"