Workbook backup before opening?

  • Thread starter Thread starter toothfish__
  • Start date Start date
T

toothfish__

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
 
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.
 
Rob van Gelder said:
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
Click to expand...

thanks Rob,

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

appreciate your help.
 
David McRitchie said:
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
Click to expand...

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 said:
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.
Click to expand...

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.
 
Back
Top