Change location of auto saved back up copy of shared workbook?

[Guest]

We have a shared workbook here and are finding that someone is accessing the
back up rather than the orginial doc and it is creating problems. We have
set the auto save but would like to change the default location or password
protect the back up copy only.

On an aside, we have forgotten how to set the auto save, so a reminder on
that would be great too. Thanks, Roxanne

 

[Bryan Hessey]

In Tools, Options, Save, enter the required AutoRecover save location.

--

 

[Dave Peterson]

Autosave overwrites the original file.

Autorecovery writes to a user selectable folder--but the file is destroyed when
that workbook is closed successfully.

Autosave came with xl2k and below. Autorecovery is included in xl2002+. They
actually serve different purposes.

Any chance that you have some other file that's being accessed.

There is an option to create a backup file when you save the workbook.
File|saveAs|tools|General Options|Always create backup.

This option creates files with extensions of .xlk.

I'm not sure which one of these (if any) you're seeing.

 

[Guest]

I am seeing the last option you spoke of: xlk. Essentially, we want to have
a back up made so that if anyone wrecks the original, there is a back up we
can use. Some how or way someone is accessing that back up? or so I have
been told and using the back up as the original doc. Our hope is to find a
way to create an auto back up but passoword protect it or send it to a secure
location. Thanks for the help! Roxanne

 

[Dave Peterson]

If the .xlk backup file is causing trouble, then I'd turn that option off.

If macros are enabled, you have at least a couple of choices.

#1. Provide a macro that does the save and a savecopyas to the folder (not
publicized, but still public).

Option Explicit
Sub SpecialSaveTheFile()

Dim myFileName As String

With ThisWorkbook
'save it once
On Error Resume Next
.Save
If Err.Number <> 0 Then
MsgBox "Save failed--contact Roxanne"
Err.Clear
Exit Sub
End If
On Error GoTo 0

'remove the .xls from the name
myFileName = Left(.Name, Len(.Name) - 4)
On Error Resume Next
.SaveCopyAs Filename:="C:\myfolder\" & myFileName _
& "_" & Format(Now, "yyyymmdd_hhmmss") & ".xls"

If Err.Number <> 0 Then
MsgBox "SaveCopyAs failed--contact Roxanne"
Err.Clear
Exit Sub
End If
On Error GoTo 0

End With

End Sub

This code goes into a General Module.

I used "c:\myfolder" for testing. This folder will accumulate files based on
time/date and will have to be cleaned up every so often.

#2. If you don't want to rely on the user clicking a button to save it, you
could have the BeforeSave event call this kind of code.

This code goes behind the ThisWorkbook module:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False
Call SpecialSaveTheFile
Application.EnableEvents = True
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