0

In Excel 2007 in the Save As box there is an option to 'Create a Backup' which simply backs up the file whenever it is saved. Unfortunately it backs up the file to the same directory as the original.
Is there a simple way to change this directory to another drive / folder? I have messed about with macros to do this, coming up with:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Saves the current file to a backup folder and the default folder
'Note that any backup is overwritten
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs Filename:="T:\TEC_SERV\Backup file folder - DO NOT DELETE\" & _ ActiveWorkbook.Name
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub

This creates a backup of the file ok the first time, however if this is tried again I get:
Run-Time Error '1004';
Microsoft Office Excel cannot access the file 'T:\TEC_SERV\Backup file folder - DO NOT DELETE\Test Macro Sheet.xlsm. There are several possible reasons:
The file name or path does not exist
The file is being used by another program
The workbook you are trying to save has the same name as a...

I know the path is correct, I also know that the file is not open anywhere else. The workbook has the same name as the one I'm trying to save over but it should just overwrite.

I have posted the question about the coding on Stack Overflow but wondered if there is an easier way to do this.

Any help would be much appreciated. Joe

Improve this question
2
  • For some odd reason, I don't see that option on my Excel '07. What version do you have?
    – Isxek
    Commented May 14, 2010 at 15:19
  • 1
    Does not help answer the OP, but to answer lsxek: When you go to Save As..., click on Tools button, General options. This is where you get the tickbox for "Always create a backup" as well as password options. The option is per-document regardless of who edits is, not for all spreadsheets you create. It is not really creating a backup (it does not do an extra write operation), it is more like this: Normal file save creates a new temporary file, and once it is completely saved it deletes the original and renames the new copy. Backup simply renames the original instead of deleting it.
    – AdamV
    Commented May 17, 2010 at 14:58

2 Answers 2

Reset to default
1

The workbook you are trying to save has the same name as a...

I am assuming this ends in "as a workbook that is already open". I don't know why this is a requirement, but Excel won't touch two spreadsheets with the same filename. You might want to try & Workbook.Name & ".bak" in your code.

Although you should note that you are probably going about this the wrong way. What you are trying to do is called "version control", and you should look into Shadow Copies and a source code repository (even though this Excel spreadsheet is not source code, you can still version the file). This is most likely "The Right Thing To Do (TM)"

Improve this answer
3
  • I have ammended the code to contain a .bak, on the first instance it runs it gives the desired result. However every time it is run subsequently it throws up the same error as before. Your help is appreciated
    – Joe Taylor
    Commented May 17, 2010 at 9:13
  • Use <a href="technet.microsoft.com/en-us/sysinternals/…> to make sure the copy is not being held open, also that the file is not flagged as read only. I would think that SaveCopyAs may not overwrite an existing file. You could delete the file before you run SaveCopyAs, or consider naming the backups as Filename.YYYY.MM.DD.HH.MM.SS.xls. Although I cannot stress how strongly you should be using a version control system.
    – ta.speot.is
    Commented May 17, 2010 at 10:36
  • Thanks for your help. Unfortunately we are working on a small segement of the corporate university system and simply have no say over what systems are in place. The backup in different directory is only to keep a version of the file in another directory in case someone accientally deletes it.
    – Joe Taylor
    Commented May 19, 2010 at 14:04
0

Ok so the long and the short of it seems that there is no easy way to do this. There isn't the function present in Excel to allow the backups to be saved to a different folder. What is required is a fairly simple Visual Basic Macro - similar to the one outlined above. I have Taken Taspeotis's ideas into account and so have upvoted his answer. I have not given the answer as correct as it does not answer the question. I do appreciate the time given though.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .