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

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
wrote:

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

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

"Gord Dibben" wrote:

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
wrote:

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

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



"MCC Wong" wrote:

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

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

On Wed, 16 Jan 2008 04:16:00 -0800, MCC Wong
wrote:

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

"Gord Dibben" wrote:

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
wrote:

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

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

"Gord Dibben" wrote:

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

On Wed, 16 Jan 2008 04:16:00 -0800, MCC Wong
wrote:

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

"Gord Dibben" wrote:

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
wrote:

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

You changed Gord's original code:

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

ByVal SaveAsUi are two separate words.

MCC Wong wrote:

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

"Gord Dibben" wrote:

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

On Wed, 16 Jan 2008 04:16:00 -0800, MCC Wong
wrote:

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

"Gord Dibben" wrote:

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
wrote:

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





--

Dave Peterson

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

On Fri, 18 Jan 2008 09:18:06 -0600, Dave Peterson
wrote:

You changed Gord's original code:

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

ByVal SaveAsUi are two separate words.

MCC Wong wrote:

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

"Gord Dibben" wrote:

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

On Wed, 16 Jan 2008 04:16:00 -0800, MCC Wong
wrote:

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

"Gord Dibben" wrote:

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
wrote:

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

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

Gord Dibben wrote:

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

On Fri, 18 Jan 2008 09:18:06 -0600, Dave Peterson
wrote:

You changed Gord's original code:

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

ByVal SaveAsUi are two separate words.

MCC Wong wrote:

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

"Gord Dibben" wrote:

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

On Wed, 16 Jan 2008 04:16:00 -0800, MCC Wong
wrote:

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

"Gord Dibben" wrote:

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
wrote:

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





--

Dave Peterson

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

On Fri, 18 Jan 2008 17:03:05 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Fri, 18 Jan 2008 09:18:06 -0600, Dave Peterson
wrote:

You changed Gord's original code:

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

ByVal SaveAsUi are two separate words.

MCC Wong wrote:

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

"Gord Dibben" wrote:

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

On Wed, 16 Jan 2008 04:16:00 -0800, MCC Wong
wrote:

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

"Gord Dibben" wrote:

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
wrote:

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