Save Backup On Close

[mark n]

I want to automatically save a backup copy of a file when the workbook is closed. I can't get the "CreateBackup" command to work in the "Workbook_BeforeClose" subroutine. Any suggestions?

 

[KniteMare]

I use this to back up the staff scheduling program to a file named after the date they did the attendance sheet for.


If MsgBox("Back-Up this Roster", vbYesNo) = vbYes Then
dy = Worksheets("ReSet").Range("RosterDate")
dy = Format(dy, "mm-dd-yy")
dy = (ActiveWorkbook.Path & "Back-Ups Roster for " & dy & ".XLS")
ActiveWorkbook.SaveCopyAs FileName:=dy
Else
End If


The magic part here is the SaveCopyAs. It saves a copy and will NOT askyou if is is OK to overwrite any existing file with the same name.

Use the F1 key to see what the various parts of the routine are doing.

Yours in EXCELent Frustration

KniteMare


So much time, so little money.
This message was edited by KniteMare on 2002-09-10 12:59

 

[Jack in the UK]

Hi

A different approach, using time so you can save as often as you like, can be back up as well i guess on command button.

Place this script in a standard module, post if you require more assistance

HTH ......... Jack
(Tested on Win XP PRO and Excel XP Full Retail)

Sub Back_Up()
'From source code on MrExcel and edited by Jack in the UK
'MrExecl 10 Sept 202
UC = Format$(Now, "hh mm ss")
United_Colours_of_BRIXTON = Format$(Now, "d mmmm yyyy")

If MsgBox("Back-Up this Roster", vbYesNo) = vbYes Then

ChDir "C:Jacks Back Up"
' You will need to have this DIR edited to your liking!!!

With ActiveSheet
.SaveAs Filename _
:=(Trim(ActiveSheet.Name & _
" " & _
United_Colours_of_BRIXTON & _
" Time " & _
UC & _
".XLS"))
End With

Else
End If

End Sub

 

[skadan]

In my "onclose" routine, I add in the line :

Activeworkbook.SaveCopyAs"fath'filename'"

However, it looses the file association (.xls) which may be good or bad, depending on your point of view... you can still open it either from within an open XL session, or "openwith" when double-clicking the file icon.

 

[Jack in the UK]

If you use the script i have given and make the file or your file this extention will not be an issue its taken care off

HTH