Creating a monthly back up using Auto_close??

S

Simon Lloyd

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
 
A

Andy Wiggins

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"
 
N

Nigel

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

Andy Wiggins

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

Nigel

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
 
A

Andy Wiggins

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"
 
S

Simon Lloyd

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
 
N

Nigel

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
 
D

David

Nigel wrote
Andy Wiggins original reply meets your needs which I have re-produced
below.
Click to expand...

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

Andy Wiggins

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"
 
Top