can I create backup file in separate folder from the original?

[MCC Wong]

Is is possible to automatically create a backup file once we update an excel
file in a separate folder from the folder with the original excel file. The
reason is that we are afraid that someone might delete the folder
accidentally and lost all the both the original file and the backup file.

We are using Microsoft Excel 2003

 

[Gord Dibben]

Only through VBA code which makes a backup copy of the file in another folder of
your choice when saved.

Private Sub Workbook_BeforeSave(ByVal _
SaveAsUI As Boolean, Cancel As Boolean)
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs Filename:="C:\Gordstuff\" & _
ActiveWorkbook.Name
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub

On the other hand, why don't you just regularly make a backup of the folder?

You should be doing that with all your data files and folders as a routine.


Gord Dibben MS Excel MVP

On Tue, 15 Jan 2008 03:59:00 -0800, MCC Wong <MCC

 

[MCC Wong]

Thanks Dibben,

I've tried the VBA code, but not successful. Since I'm not familiar with
using VBA code or writing macro, can you teach me how to do it??

I have created a shared electronic bank book namely 'HSBC.xls' in J drive
and when I save this file, I would like to make a backup copy in C drive
namely 'Backup HSBC', but I don't know exactly how to write VBA code.

Should I use the macro function or the Visual Basic Editor under Tools??
Please teach me how to write the macro or VBA code!!

I've tried the following as suggested but won't work:

Sub HSBC (ByValSaveASUI As Boolean, Cancel As Boolean)
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs "C:\Backup HSBC.xls" & ActiveWorkbook.Name
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub

What have I done wrong?? please advice!!

MCC

 

[CLR]

If you are working for a Company, with a MIS department, they usually have a
regular schedule of making backups of all files. Check with them, and
negotiate your needs.

Vaya con Dios,
Chuck, CABGx3

 

[Gord Dibben]

You have made too many changes to the code I posted.

Try this one and do not make any more changes.

Private Sub Workbook_BeforeSave (ByValSaveASUI As Boolean, Cancel As Boolean)
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs "C:\Backup HSBC\" & ActiveWorkbook.Name
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub

Copy this code as is then in top left corner of Excel Menu bar left of "File"
right-click on the Excel Icon and "View Code"

Paste the code into that module.

Alt + q to return to Excel window. Save your workbook.

Go look in BackupHSBC folder.


Gord

 

[MCC Wong]

Dear Dibben,

I've just copied your code but the feedback shows the following message:
"Complie error:
Procedure declaration does not match description of event or procedure
having the same name."
and the first code "Private Sub Workbook_BeforeSave (ByValSaveASUI As
Boolean, Cancel As Boolean)" is being highlighted.

Please advice.

MCC

 

[Dave Peterson]

You changed Gord's original code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

ByVal SaveAsUi are two separate words.

 

[Gord Dibben]

I think OP is typing, rather than just copy/pasting.

 

[Dave Peterson]

If it's more than 3 characters, that's usually a mistake! <vbg>

 

[Gord Dibben]

Although I must admit, I compounded the error by copying some of his first
incorrect attempt at making changes by leaving the "ByValSaveASUI" in my second
posting.


Gord