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
Save Location for auto created backup file
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Save Location for auto created backup file
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
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
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Save Location for auto created backup file
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
We are wondering if the save location can be changed for the backup file that is created.
Thanks,
Brad
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Save Location for auto created backup file
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
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
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Save Location for auto created backup file
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
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
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Save Location for auto created backup file
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:
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
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Save Location for auto created backup file
Alrighty,
Thanks for the quick response and code.
Brad
Thanks for the quick response and code.
Brad
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Save Location for auto created backup file
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
thanks
BRad
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Save Location for auto created backup file
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
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Save Location for auto created backup file
Thanks much.
Brad
Brad