Save Location for auto created backup file

[bradjedis]

Happy day!

I have the situation where I wish to have the backup of a file directed to a different save location than that of the original file.

Is there a method to accomplish this task?

Using Excel 2007

Thanks,
Brad

[Rudi]

Hi Brad,

Are you refering to the auto backup that Excel creates with its autosave feature, or is this backup you refer to being saved via a macro or add-in in Excel? Please provide a bit more specific info that can help to direct us in providing you an answer.

Thanks

[bradjedis]

I am referring to when using the save or save as from the dropdown. then selecting Tools, General Options. The dialog box allows for clicking "Always create Backup".

We are wondering if the save location can be changed for the backup file that is created.


Thanks,
Brad

[HansV]

No, the backup created by that option will always be in the same folder as the workbook. You can't change that.

You could write a macro to create a backup copy in another folder, using

Dim strBackupFilename As String
' Set up the path and name here
strBackupFilename = ...
' Save a copy of the active workbook
ActiveWorkbook.SaveCopyAs strBackupFilename

[bradjedis]

Cool,
So, we could set this to run right after opening the document? If so , would I set this as Option Explicit and in the This workbook area?

Brad

[HansV]

Option Explicit is a compiler directive that should be at the top of every module. You can have it inserted automatically by ticking the check box "Require Variable Declaration" in Tools | Options... in the Visual Basic Editor.
This option forces you to declare all variables explicitly; this is very useful because it protects you from some (but not all) unintended errors.

You could use the code in the BeforeSave event of the workbook, in the ThisWorkbook module:

Code: Select all

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Dim strBackupFilename As String
  ' Set up the path and name here
  strBackupFilename = ...
  ' Save a copy of the active workbook
  ActiveWorkbook.SaveCopyAs strBackupFilenam
End Sub

[bradjedis]

Alrighty,

Thanks for the quick response and code.

Brad

[bradjedis]

In relation to the last message fron Hans, we need this to run on file open, before any changes.... We have several folks that can edit the file...

thanks
BRad

[HansV]

You can place the same code in the Workbook_Open event, also in the ThisWorkbook module:

Code: Select all

Private Sub Workbook_Open()
  Dim strBackupFilename As String
  ' Set up the path and name here
  strBackupFilename = ...
  ' Save a copy of the active workbook
  ActiveWorkbook.SaveCopyAs strBackupFilenam
End Sub

[bradjedis]

Thanks much.

Brad