Hi all,

Can anyone tell me how to create a monthly backup of my file whe
autoclose is initiated? the file will most probably be used most day
of the week so after a month has passed when it is next closed i woul
like a back up copy to made to P:\public\training\development, bu
every month it makes a back up copy it needs to have a different dat
say "trainingBkUp7.7.04" and next month it would b
"trainingBkUp7.8.04" etc.

Can anyone help?

Simon.

P.S the Auto_close just updates the file and saves it to its curren
location and its current filename (Workbook.Save

--
Message posted from http://www.ExcelForum.com

Put this code in your "Thisworkbook" module.
It tests to see if tomorrow is the same month as today.
If it is this just performs a save.
If it isn't, then a dated backup is also created.

'' - - - - Code begins
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lDat_Today As Date
Dim lDat_Tomorrow As Date
Dim lStr_TargetFile As String

lDat_Today = Date
lDat_Tomorrow = Date + 1

With ThisWorkbook
If Month(lDat_Today) = Month(lDat_Tomorrow) Then
'' Do nothing, we're still in the same month
Else
'' Tomorrow is a new month so make a backup today
.SaveCopyAs ThisWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, InStr(1, LCase(ThisWorkbook.Name),
".xls") - 1) & _
" - " & Format(Now, "yyyymmdd") & ".xls"
End If

'' Save the original
.Save

End With
End Sub
'' - - - - Code Ends

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Simon Lloyd " wrote in message
...
Hi all,

Can anyone tell me how to create a monthly backup of my file when
autoclose is initiated? the file will most probably be used most days
of the week so after a month has passed when it is next closed i would
like a back up copy to made to P:\public\training\development, but
every month it makes a back up copy it needs to have a different date
say "trainingBkUp7.7.04" and next month it would be
"trainingBkUp7.8.04" etc.

Can anyone help?

Simon.

P.S the Auto_close just updates the file and saves it to its current
location and its current filename (Workbook.Save)


---
Message posted from http://www.ExcelForum.com/

Nice!

But a backup for the current month is only made at the end of the month. So
it is really making an archive copy not really a back up.

If a back up is required I would recommend that it is made as the workbook
is opended, overwriting the previous version. The user then has the chance
to undo any changes made to the current session even after saving changes to
the current workbook.

So in "Thisworkbook" module
Private Sub Workbook_Open()
With ThisWorkbook
.SaveCopyAs ThisWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, InStr(1, LCase(ThisWorkbook.Name),
".xls") - 1) & ".bak"
End With
End Sub

Cheers
Nigel

"Andy Wiggins" <xx wrote in message
...
Put this code in your "Thisworkbook" module.
It tests to see if tomorrow is the same month as today.
If it is this just performs a save.
If it isn't, then a dated backup is also created.

'' - - - - Code begins
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lDat_Today As Date
Dim lDat_Tomorrow As Date
Dim lStr_TargetFile As String

lDat_Today = Date
lDat_Tomorrow = Date + 1

With ThisWorkbook
If Month(lDat_Today) = Month(lDat_Tomorrow) Then
'' Do nothing, we're still in the same month
Else
'' Tomorrow is a new month so make a backup today
.SaveCopyAs ThisWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, InStr(1, LCase(ThisWorkbook.Name),
".xls") - 1) & _
" - " & Format(Now, "yyyymmdd") & ".xls"
End If

'' Save the original
.Save

End With
End Sub
'' - - - - Code Ends

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Simon Lloyd " wrote in
message
...
Hi all,

Can anyone tell me how to create a monthly backup of my file when
autoclose is initiated? the file will most probably be used most days
of the week so after a month has passed when it is next closed i would
like a back up copy to made to P:\public\training\development, but
every month it makes a back up copy it needs to have a different date
say "trainingBkUp7.7.04" and next month it would be
"trainingBkUp7.8.04" etc.

Can anyone help?

Simon.

P.S the Auto_close just updates the file and saves it to its current
location and its current filename (Workbook.Save)


---
Message posted from http://www.ExcelForum.com/

Or, make a backup/archive copy whenever the source book is closed.

This routine saves date and timed versions:

'' - - - - Code begins
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lStr_TargetFile As String

With ThisWorkbook
.SaveCopyAs ThisWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, _
InStr(1, LCase(ThisWorkbook.Name), ".xls") - 1) & _
" - " & Format(Now, "yyyymmdd hhmmss") & ".xls"
'' Save the original
.Save

End With
End Sub
'' - - - - Code Ends
--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Nigel" wrote in message
...
Nice!

But a backup for the current month is only made at the end of the month.
So
it is really making an archive copy not really a back up.

If a back up is required I would recommend that it is made as the workbook
is opended, overwriting the previous version. The user then has the
chance
to undo any changes made to the current session even after saving changes
to
the current workbook.

So in "Thisworkbook" module
Private Sub Workbook_Open()
With ThisWorkbook
.SaveCopyAs ThisWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, InStr(1, LCase(ThisWorkbook.Name),
".xls") - 1) & ".bak"
End With
End Sub

Cheers
Nigel

"Andy Wiggins" <xx wrote in message
...
Put this code in your "Thisworkbook" module.
It tests to see if tomorrow is the same month as today.
If it is this just performs a save.
If it isn't, then a dated backup is also created.

'' - - - - Code begins
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lDat_Today As Date
Dim lDat_Tomorrow As Date
Dim lStr_TargetFile As String

lDat_Today = Date
lDat_Tomorrow = Date + 1

With ThisWorkbook
If Month(lDat_Today) = Month(lDat_Tomorrow) Then
'' Do nothing, we're still in the same month
Else
'' Tomorrow is a new month so make a backup today
.SaveCopyAs ThisWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, InStr(1,
LCase(ThisWorkbook.Name),
".xls") - 1) & _
" - " & Format(Now, "yyyymmdd") & ".xls"
End If

'' Save the original
.Save

End With
End Sub
'' - - - - Code Ends

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Simon Lloyd " wrote in
message
...
Hi all,

Can anyone tell me how to create a monthly backup of my file when
autoclose is initiated? the file will most probably be used most days
of the week so after a month has passed when it is next closed i would
like a back up copy to made to P:\public\training\development, but
every month it makes a back up copy it needs to have a different date
say "trainingBkUp7.7.04" and next month it would be
"trainingBkUp7.8.04" etc.

Can anyone help?

Simon.

P.S the Auto_close just updates the file and saves it to its current
location and its current filename (Workbook.Save)


---
Message posted from http://www.ExcelForum.com/

Yep, could do it in a one'r but the original and the "backup" are identical
leaving no recovery after saving. I guess it depends on the level of backup
protection the OP requires. All options are covered depending on his needs.

Cheers
Nigel

"Andy Wiggins" wrote in message
...
Or, make a backup/archive copy whenever the source book is closed.

This routine saves date and timed versions:

'' - - - - Code begins
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lStr_TargetFile As String

With ThisWorkbook
.SaveCopyAs ThisWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, _
InStr(1, LCase(ThisWorkbook.Name), ".xls") - 1) & _
" - " & Format(Now, "yyyymmdd hhmmss") & ".xls"
'' Save the original
.Save

End With
End Sub
'' - - - - Code Ends
--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Nigel" wrote in message
...
Nice!

But a backup for the current month is only made at the end of the month.
So
it is really making an archive copy not really a back up.

If a back up is required I would recommend that it is made as the
workbook
is opended, overwriting the previous version. The user then has the
chance
to undo any changes made to the current session even after saving
changes
to
the current workbook.

So in "Thisworkbook" module
Private Sub Workbook_Open()
With ThisWorkbook
.SaveCopyAs ThisWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, InStr(1, LCase(ThisWorkbook.Name),
".xls") - 1) & ".bak"
End With
End Sub

Cheers
Nigel

"Andy Wiggins" <xx wrote in message
...
Put this code in your "Thisworkbook" module.
It tests to see if tomorrow is the same month as today.
If it is this just performs a save.
If it isn't, then a dated backup is also created.

'' - - - - Code begins
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lDat_Today As Date
Dim lDat_Tomorrow As Date
Dim lStr_TargetFile As String

lDat_Today = Date
lDat_Tomorrow = Date + 1

With ThisWorkbook
If Month(lDat_Today) = Month(lDat_Tomorrow) Then
'' Do nothing, we're still in the same month
Else
'' Tomorrow is a new month so make a backup today
.SaveCopyAs ThisWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, InStr(1,
LCase(ThisWorkbook.Name),
".xls") - 1) & _
" - " & Format(Now, "yyyymmdd") & ".xls"
End If

'' Save the original
.Save

End With
End Sub
'' - - - - Code Ends

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Simon Lloyd " wrote in
message
...
Hi all,

Can anyone tell me how to create a monthly backup of my file when
autoclose is initiated? the file will most probably be used most
days
of the week so after a month has passed when it is next closed i
would
like a back up copy to made to P:\public\training\development, but
every month it makes a back up copy it needs to have a different
date
say "trainingBkUp7.7.04" and next month it would be
"trainingBkUp7.8.04" etc.

Can anyone help?

Simon.

P.S the Auto_close just updates the file and saves it to its current
location and its current filename (Workbook.Save)


---
Message posted from http://www.ExcelForum.com/

It's not the same as the previous backup, or the one before that, or the one
before that .....

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Nigel" wrote in message
...
Yep, could do it in a one'r but the original and the "backup" are
identical
leaving no recovery after saving. I guess it depends on the level of
backup
protection the OP requires. All options are covered depending on his
needs.

Cheers
Nigel

"Andy Wiggins" wrote in message
...
Or, make a backup/archive copy whenever the source book is closed.

This routine saves date and timed versions:

'' - - - - Code begins
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lStr_TargetFile As String

With ThisWorkbook
.SaveCopyAs ThisWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, _
InStr(1, LCase(ThisWorkbook.Name), ".xls") - 1) & _
" - " & Format(Now, "yyyymmdd hhmmss") & ".xls"
'' Save the original
.Save

End With
End Sub
'' - - - - Code Ends
--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Nigel" wrote in message
...
Nice!

But a backup for the current month is only made at the end of the
month.
So
it is really making an archive copy not really a back up.

If a back up is required I would recommend that it is made as the
workbook
is opended, overwriting the previous version. The user then has the
chance
to undo any changes made to the current session even after saving
changes
to
the current workbook.

So in "Thisworkbook" module
Private Sub Workbook_Open()
With ThisWorkbook
.SaveCopyAs ThisWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, InStr(1,
LCase(ThisWorkbook.Name),
".xls") - 1) & ".bak"
End With
End Sub

Cheers
Nigel

"Andy Wiggins" <xx wrote in message
...
Put this code in your "Thisworkbook" module.
It tests to see if tomorrow is the same month as today.
If it is this just performs a save.
If it isn't, then a dated backup is also created.

'' - - - - Code begins
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lDat_Today As Date
Dim lDat_Tomorrow As Date
Dim lStr_TargetFile As String

lDat_Today = Date
lDat_Tomorrow = Date + 1

With ThisWorkbook
If Month(lDat_Today) = Month(lDat_Tomorrow) Then
'' Do nothing, we're still in the same month
Else
'' Tomorrow is a new month so make a backup today
.SaveCopyAs ThisWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, InStr(1,
LCase(ThisWorkbook.Name),
".xls") - 1) & _
" - " & Format(Now, "yyyymmdd") & ".xls"
End If

'' Save the original
.Save

End With
End Sub
'' - - - - Code Ends

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Simon Lloyd " wrote in
message
...
Hi all,

Can anyone tell me how to create a monthly backup of my file when
autoclose is initiated? the file will most probably be used most
days
of the week so after a month has passed when it is next closed i
would
like a back up copy to made to P:\public\training\development, but
every month it makes a back up copy it needs to have a different
date
say "trainingBkUp7.7.04" and next month it would be
"trainingBkUp7.8.04" etc.

Can anyone help?

Simon.

P.S the Auto_close just updates the file and saves it to its
current
location and its current filename (Workbook.Save)


---
Message posted from http://www.ExcelForum.com/

Guys!,

Thanks for all the info.....but from a noo b's point of view....help
i'm easily confused. Which code should i use?, as the workbook stands
want it to save the current workbook to the current location everytim
its closed, but once a month (or every 30 days if its easier) i woul
like the work book to ALSO save a backup copy with the name of the fil
and dated the day it was made.

So whose code should i use??

Thanks,

Simo

--
Message posted from http://www.ExcelForum.com

Andy Wiggins original reply meets your needs which I have re-produced below.
Cheers Nigel

'' - - - - Code begins
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lDat_Today As Date
Dim lDat_Tomorrow As Date
Dim lStr_TargetFile As String

lDat_Today = Date
lDat_Tomorrow = Date + 1

With ThisWorkbook
If Month(lDat_Today) = Month(lDat_Tomorrow) Then
'' Do nothing, we're still in the same month
Else
'' Tomorrow is a new month so make a backup today
.SaveCopyAs ThisWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, InStr(1, LCase(ThisWorkbook.Name),
".xls") - 1) & _
" - " & Format(Now, "yyyymmdd") & ".xls"
End If

'' Save the original
.Save

End With
End Sub
'' - - - - Code Ends

"Simon Lloyd " wrote in message
...
Guys!,

Thanks for all the info.....but from a noo b's point of view....help!
i'm easily confused. Which code should i use?, as the workbook stands i
want it to save the current workbook to the current location everytime
its closed, but once a month (or every 30 days if its easier) i would
like the work book to ALSO save a backup copy with the name of the file
and dated the day it was made.

So whose code should i use??

Thanks,

Simon


---
Message posted from http://www.ExcelForum.com/

Nigel wrote

Andy Wiggins original reply meets your needs which I have re-produced
below.

What if I close the file on Friday the 29th and open it back up on Monday
the 1st? Will it still work?

--
David

Good point.

This is an amended version of my original post that takes account of
weekends.

'' - - - - Code begins
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lDat_Today As Date
Dim lDat_Tomorrow As Date
Dim lStr_TargetFile As String

lDat_Today = Date
If "Fri" = Format(Date, "ddd") Then
lDat_Tomorrow = Date + 3
Else
lDat_Tomorrow = Date + 1
End If

With ThisWorkbook
If Month(lDat_Today) = Month(lDat_Tomorrow) Then
'' Do nothing, we're still in the same month
Else
'' Tomorrow is a new month so make a backup today
.SaveCopyAs ThisWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, InStr(1, LCase(ThisWorkbook.Name),
".xls") - 1) & _
" - " & Format(Now, "yyyymmdd") & ".xls"
End If

'' Save the original
.Save

End With
End Sub
'' - - - - Code Ends

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"David" wrote in message
...
Nigel wrote

Andy Wiggins original reply meets your needs which I have re-produced
below.

What if I close the file on Friday the 29th and open it back up on Monday
the 1st? Will it still work?

--
David