Discussion:
can I create backup file in separate folder from the original?
(too old to reply)
MCC Wong
2008-01-15 11:59:00 UTC
Permalink
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
2008-01-15 19:57:21 UTC
Permalink
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
Post by 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
MCC Wong
2008-01-16 12:16:00 UTC
Permalink
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
Post by 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
Post by 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
2008-01-17 00:11:15 UTC
Permalink
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
Post by 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!!
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
Post by 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
Post by 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
MCC Wong
2008-01-18 06:11:01 UTC
Permalink
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
Post by 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
Post by 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!!
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
Post by 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
Post by 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
Dave Peterson
2008-01-18 15:18:06 UTC
Permalink
You changed Gord's original code:

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

ByVal SaveAsUi are two separate words.
Post by MCC Wong
Dear Dibben,
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
Post by 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
Post by 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!!
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
Post by 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
Post by 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
--
Dave Peterson
Gord Dibben
2008-01-19 01:03:05 UTC
Permalink
I think OP is typing, rather than just copy/pasting.
Post by Dave Peterson
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ByVal SaveAsUi are two separate words.
Post by MCC Wong
Dear Dibben,
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
Post by 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
Post by 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!!
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
Post by 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
Post by 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
Dave Peterson
2008-01-19 01:05:17 UTC
Permalink
If it's more than 3 characters, that's usually a mistake! <vbg>
Post by Gord Dibben
I think OP is typing, rather than just copy/pasting.
Post by Dave Peterson
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ByVal SaveAsUi are two separate words.
Post by MCC Wong
Dear Dibben,
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
Post by 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
Post by 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!!
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
Post by 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
Post by 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
--
Dave Peterson
Gord Dibben
2008-01-19 01:06:59 UTC
Permalink
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
Post by Gord Dibben
I think OP is typing, rather than just copy/pasting.
Post by Dave Peterson
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ByVal SaveAsUi are two separate words.
Post by MCC Wong
Dear Dibben,
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
Post by 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
Post by 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!!
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
Post by 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
Post by 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
CLR
2008-01-16 13:46:05 UTC
Permalink
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
Post by 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
Loading...