Backup file attributes

S

sewilli

I like having Excel create a backup file when I save my workbook (save
options) but don't like the clutter this creates in my save folders. Is
there a way to have the backup files automatically saved as hidden files? I
can manually change the file properties after the fact but this gets undone
the next time I save the file.
 
D

Dave Peterson

I would think that you'd have even more clutter--except it would be invisible
(if you hide hidden files, that is).

How about this. You add a macro to your personal.xls workbook (where you store
your nice generic macros) that saves a copy to a backup folder directly under
the folder holding the activeworkbook.

You can assign it to a nice shortcut key
(ctrl-shift-s since ctrl-s does a normal save)

Then you can clean up that backup folder when ever you felt the need.

Option Explicit
Sub SaveAndSaveACopy()

Dim myPath As String
Dim myBackupName As String

myBackupName = "Backup"

With ActiveWorkbook
myPath = .Path
If myPath = "" Then
MsgBox "Please save this workbook normally at least once!"
Exit Sub
End If

On Error Resume Next
MkDir myPath & "\" & myBackupName
On Error GoTo 0

On Error Resume Next
.Save 'save
If Err.Number <> 0 Then
MsgBox "Save failed--backup not tried" & vbLf & _
Err.Number & "--" & Err.Description
Err.Clear
Exit Sub
End If
On Error GoTo 0

On Error Resume Next
.SaveCopyAs Filename:=myPath & "\" & myBackupName & "\" & .Name
If Err.Number <> 0 Then
MsgBox "SaveCopyAs failed" & vbLf & _
Err.Number & "--" & Err.Description
Err.Clear
Exit Sub
End If
On Error GoTo 0
End With

End Sub

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