I am trying to create a backup of a workbook before
opening it for new updates, I think I have the code right
but am not sure where to place this code so that it
executes before the "Current" workbook opens? my code is
as follows;
==
Dim Source, Destination
Source = "C:\Current.xls"
Destination = "C:\Backup\Current_" &Format(Date,"YYMMDD")
& ".xls"
FileCopy Source, Destination
==

Any advice, help will be greatly appreciated.

Thanks,

Toothfish,

In the Code for ThisWorkbook:
Private Sub Workbook_Open()
Dim Source, Destination
Source = "C:\Current.xls"
Destination = "C:\Backup\Current_" & Format(Date, "YYMMDD") & ".xls"
FileCopy Source, Destination
End Sub

Or under Auto_Open in a Module

Rob

"toothfish__" wrote in message
...
I am trying to create a backup of a workbook before
opening it for new updates, I think I have the code right
but am not sure where to place this code so that it
executes before the "Current" workbook opens? my code is
as follows;
==
Dim Source, Destination
Source = "C:\Current.xls"
Destination = "C:\Backup\Current_" &Format(Date,"YYMMDD")
& ".xls"
FileCopy Source, Destination
==

Any advice, help will be greatly appreciated.

Thanks,

Suggest you use a 4 digit year to make the date less
ambiguous. perhaps something like
dname = "c:\mybackup\B" & Format(Now(), "yyyy_mmdd")


You could use BeforeSave described in
Backup your files, always take backups
Location: http://www.mvps.org/dmcritchie/excel/backup.htm

and in http://www.mvps.org/dmcritchie/excel/events.htm

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"toothfish__" wrote in message ...
I am trying to create a backup of a workbook before
opening it for new updates, I think I have the code right
but am not sure where to place this code so that it
executes before the "Current" workbook opens? my code is
as follows;
==
Dim Source, Destination
Source = "C:\Current.xls"
Destination = "C:\Backup\Current_" &Format(Date,"YYMMDD")
& ".xls"
FileCopy Source, Destination
==

Any advice, help will be greatly appreciated.

Thanks,

Is this code in your c:\current.xls workbook?

If yes, then I don't think filecopy will work with an open file.

You may want to look at savecopyas in vba's help:

Option Explicit
Private Sub Workbook_Open()
ThisWorkbook.SaveCopyAs "C:\backup\Current_" _
& Format(Date, "YYMMDD") & ".xls"
End Sub

If you're using the code in a different workbook, then never mind.

toothfish__ wrote:

I am trying to create a backup of a workbook before
opening it for new updates, I think I have the code right
but am not sure where to place this code so that it
executes before the "Current" workbook opens? my code is
as follows;
==
Dim Source, Destination
Source = "C:\Current.xls"
Destination = "C:\Backup\Current_" &Format(Date,"YYMMDD")
& ".xls"
FileCopy Source, Destination
==

Any advice, help will be greatly appreciated.

Thanks,

--

Dave Peterson

"Rob van Gelder" wrote in message ...
Toothfish,

In the Code for ThisWorkbook:
Private Sub Workbook_Open()
Dim Source, Destination
Source = "C:\Current.xls"
Destination = "C:\Backup\Current_" & Format(Date, "YYMMDD") & ".xls"
FileCopy Source, Destination
End Sub

Or under Auto_Open in a Module

Rob


thanks Rob,

initially got a "permission denied" error, but modified the sequence
and works fine now.

appreciate your help.

"David McRitchie" wrote in message ...
Suggest you use a 4 digit year to make the date less
ambiguous. perhaps something like
dname = "c:\mybackup\B" & Format(Now(), "yyyy_mmdd")


You could use BeforeSave described in
Backup your files, always take backups
Location: http://www.mvps.org/dmcritchie/excel/backup.htm

and in http://www.mvps.org/dmcritchie/excel/events.htm

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm


thanks David,

have adapted the date format as you proposed and used a SaveCopyAs,
seems to work fine.

appreciate the links and your help.

Dave Peterson wrote in message ...
Is this code in your c:\current.xls workbook?

If yes, then I don't think filecopy will work with an open file.

You may want to look at savecopyas in vba's help:

Option Explicit
Private Sub Workbook_Open()
ThisWorkbook.SaveCopyAs "C:\backup\Current_" _
& Format(Date, "YYMMDD") & ".xls"
End Sub

If you're using the code in a different workbook, then never mind.


thanks Dave,

yes the code was in the c:\current.xls workbook and FileCopy then
prompted a "permission denied" error. So have adapted the SaveCopyAs
and works perfectly now.

appreciate your help.

You're welcome, thanks for the feedback on what you
actually did in your reply to my post, and to others that replied.

Such feedback is very helpful when you get a lot of replies
as to what you choose, and why, if not otherwise clear,
and especially when the person who replies might not know
if he is providing the correct answer or not.

"toothfish__" wrote in message
have adapted the date format as you proposed and used a SaveCopyAs, seems to work fine.

appreciate the links and your help.