always save back up, save as, tools, properties

  • Thread starter Thread starter Kenlyn
  • Start date Start date
K

Kenlyn

Excel 2003. I was wondering if there was a way to have the feature
"always save back up" in File >Save As > Tools > General Options > save
the file to a different folder/directory than the orginal document?

Thanks.

Kenlyn
 
No.

You can however, use before_save event code to save the file to another folder
and the original folder.

Note: will overwrite the E:\GordStuff\Backup\ file without asking.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs Filename:="E:\GordStuff\Backup\" & _
ActiveWorkbook.Name
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub


Gord Dibben Excel MVP
 
I don't think so.

But you could assign a macro that saves a copy of the activeworkbook to a backup
folder and uses a filename that includes the date and time:

Option Explicit
Sub SaveAndBackup()

Dim BackupFolder As String
Dim CopyName As String

With ActiveWorkbook

If .Path = "" Then
Application.Dialogs(xlDialogSaveAs).Show
If .Path = "" Then
MsgBox "Please save the file for the first time", _
vbExclamation, "Save First"
Exit Sub
End If
End If

If .ReadOnly Then
MsgBox "The current file is ReadOnly--Saving not done!", _
vbExclamation, "ReadOnly"
Exit Sub
End If

BackupFolder = "C:\my backups\"
If Right(BackupFolder, 1) <> "\" Then
BackupFolder = BackupFolder & "\"
End If

On Error Resume Next
MkDir BackupFolder
On Error GoTo 0

CopyName = Left(.Name, Len(.Name) - 4) & _
"_" & Format(Now, "yyyy_mm_dd_hh_mm_ss") & ".xls"

Application.StatusBar = "Saving: " & .FullName & " @ " & Now
.Save

Application.StatusBar = "Saving Copy As: " _
& BackupFolder & CopyName & " @ " & Now
.SaveCopyAs BackupFolder & CopyName

Application.StatusBar = False

MsgBox "Saved: " & .FullName & vbLf & vbLf & _
"Copy Saved As: " & BackupFolder & CopyName, _
vbInformation, "Save/SaveCopyAs"

End With

End Sub

You can go into that backup folder and clean it up when you want.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top