Save copy on Open, keeping old backup copies

[txheart]

Hi y'all,
I put this on Ozgrid yesterday but it's been over 120 views with n
answers, so I'm copying here - y'all are better anyway!
I've searched over 4 different Excel help forums trying to find a way t
do exactly what I want, but I am just not finding it. Could be I'
blind, but ...

I run a log of television & appliance returns. That's all it is, just
log of all the info. Because it has all the info that I need for th
returns (serial, customer, when/if the return was filed, etc.) it i
vitally important to my job. I've had to start from scratch once, and
don't ever want to have to do that again! The one thing that I've done
few times is change something & save it, and somehow screwed u
everything else - then I can't remember what was what & where. I mak
changes to this log daily, sometimes several times a day.

I want the log, titled RA Sheets & Log, saved in C:/Backup/Lo
Backup-mm/dd immediately upon open. That way, any changes I, or anyon
else, make can be easily undone. -Ideally-, I'd be able to actually hav
2 backup folders. One that back up every time it's opened & the othe
for an automated monthly backup. However, I only actually need the on
backup folder, I can make monthly backups myself.

I would want the last 7 backups in the folder, anything older could b
deleted. In the monthly backup I'd want one every month, like on the 1s
or whatever.

Any help y'all could give me is very much appreciated. I am a VER
inexperienced VBA & macro user. Since I'm almost 100% certain thi
solution will consist of either VBA or macros, or both, I better let yo
know to please tell me exactly where to put stuff. Telling me to inser
a module, for example, is just gonna confuse me. lol

Again, anything that y'all can do to help is appreciated. Please let m
know if you have any questions. Thanks!

K

 

[Don Guillett]

This is all you really need. It will save any file to the same folder
but a subfolder named backup (even if it doesn't exist). You really
don't need the date, etc. You could put this in the Thisworkbook
module under workbook_open and/or assign to a button or shape

Sub Backup()
On Error GoTo BackupFile
MkDir CurDir & "\Backup"
BackupFile:
With ActiveWorkbook
MyWB = .Path & "\BACKUP\" & .Name
.SaveCopyAs MyWB
.Save
End With
End Sub

 

[txheart]

;992007']This is all you really need. It will save any file to the sam
folder
but a subfolder named backup (even if it doesn't exist). You really
don't need the date, etc. You could put this in the Thisworkbook
module under workbook_open and/or assign to a button or shape

Sub Backup()
On Error GoTo BackupFile
MkDir CurDir & "\Backup"
BackupFile:
With ActiveWorkbook
MyWB = .Path & "\BACKUP\" & .Name
.SaveCopyAs MyWB
.Save
End With
End Sub

Don, thank you for your help. I'm a little confused though. I put th
code in as you directed, saved, closed, opened & closed the document
Went to find the backup but I am not seeing any type of Backup folder.
did find a backup copy, and I suppose I could just move the copy over t
the folder, but I do want to keep the last 7 backups - that way I ca
tell where/how/when a mistake was made if I need to do so. Will thi
method name the backup copies consecutively or will it write over th
last one

 

[Gord Dibben]

txheart

Have a try with this macro to save current workbook and an incremented
backup with date for name. Make alterations to myExt if running 2007
or 2010.........xlsx or xlsm and also change Len(currfile) -4 to -5


Sub namebooks_increment()
' currfile - 2012-1-3.xls
' currfile - 2012-1-3(1).xls
' currfile - 2012-1-3(2).xls
Dim myPath As String, myFile As String, myExt As String
Dim mySerial, currfile As String
currfile = ActiveWorkbook.Name
mySerial = ""
myPath = "C:\Gordstuff\"
myFile = Left(currfile, Len(currfile) - 4) _
& " - " & Format(Date, "YYYY-MM-DD")
myExt = ".xls"

' create output using sequence 1 to n if file already exists
If Len(Dir(myPath & myFile & mySerial & myExt)) > 0 Then

Do While Len(Dir(myPath & myFile & mySerial & myExt)) > 0
mySerial = "(" & Val(Mid(mySerial, 2)) + 1 & ")"
Loop

End If

'save a backup
ActiveWorkbook.SaveCopyAs Filename:=myPath & myFile & _
mySerial & myExt

'save current workbook
ActiveWorkbook.Save

End Sub



Gord

;992007']This is all you really need. It will save any file to the same
folder
but a subfolder named backup (even if it doesn't exist). You really
don't need the date, etc. You could put this in the Thisworkbook
module under workbook_open and/or assign to a button or shape

Sub Backup()
On Error GoTo BackupFile
MkDir CurDir & "\Backup"
BackupFile:
With ActiveWorkbook
MyWB = .Path & "\BACKUP\" & .Name
.SaveCopyAs MyWB
.Save
End With
End Sub

Don, thank you for your help. I'm a little confused though. I put the
code in as you directed, saved, closed, opened & closed the document.
Went to find the backup but I am not seeing any type of Backup folder. I
did find a backup copy, and I suppose I could just move the copy over to
the folder, but I do want to keep the last 7 backups - that way I can
tell where/how/when a mistake was made if I need to do so. Will this
method name the backup copies consecutively or will it write over the
last one?

 

[Gord Dibben]

Just a note............if you wanted all this done on open then call
it from Workbook_Open or just change it from a macro to a
workbook_open event


Gord

txheart

Have a try with this macro to save current workbook and an incremented
backup with date for name. Make alterations to myExt if running 2007
or 2010.........xlsx or xlsm and also change Len(currfile) -4 to -5


Sub namebooks_increment()
' currfile - 2012-1-3.xls
' currfile - 2012-1-3(1).xls
' currfile - 2012-1-3(2).xls
Dim myPath As String, myFile As String, myExt As String
Dim mySerial, currfile As String
currfile = ActiveWorkbook.Name
mySerial = ""
myPath = "C:\Gordstuff\"
myFile = Left(currfile, Len(currfile) - 4) _
& " - " & Format(Date, "YYYY-MM-DD")
myExt = ".xls"

' create output using sequence 1 to n if file already exists
If Len(Dir(myPath & myFile & mySerial & myExt)) > 0 Then

Do While Len(Dir(myPath & myFile & mySerial & myExt)) > 0
mySerial = "(" & Val(Mid(mySerial, 2)) + 1 & ")"
Loop

End If

'save a backup
ActiveWorkbook.SaveCopyAs Filename:=myPath & myFile & _
mySerial & myExt

'save current workbook
ActiveWorkbook.Save

End Sub



Gord

;992007']This is all you really need. It will save any file to the same
folder
but a subfolder named backup (even if it doesn't exist). You really
don't need the date, etc. You could put this in the Thisworkbook
module under workbook_open and/or assign to a button or shape

Sub Backup()
On Error GoTo BackupFile
MkDir CurDir & "\Backup"
BackupFile:
With ActiveWorkbook
MyWB = .Path & "\BACKUP\" & .Name
.SaveCopyAs MyWB
.Save
End With
End Sub

Don, thank you for your help. I'm a little confused though. I put the
code in as you directed, saved, closed, opened & closed the document.
Went to find the backup but I am not seeing any type of Backup folder. I
did find a backup copy, and I suppose I could just move the copy over to
the folder, but I do want to keep the last 7 backups - that way I can
tell where/how/when a mistake was made if I need to do so. Will this
method name the backup copies consecutively or will it write over the
last one?

 

[Gord Dibben]

I'll give you one more to look at..........since you wanted date and
time on your backups.

Sub namebooks_increment()
Dim myPath As String, myFile As String, myExt As String
Dim mySerial, currfile, newName As String
currfile = ActiveWorkbook.Name
myPath = "C:\Gordstuff\"
myFile = Left(currfile, Len(currfile) - 4) & " - "
newName = Format(Now, "YYYY-MM-DD hh-mm-ss")
myExt = ".xls"

'save a backup with date/time in name
ActiveWorkbook.SaveCopyAs Filename:=myPath & myFile & _
newName & myExt

'save current workbook with original name
ActiveWorkbook.Save

End Sub

Just a note............if you wanted all this done on open then call
it from Workbook_Open or just change it from a macro to a
workbook_open event


Gord

txheart

Have a try with this macro to save current workbook and an incremented
backup with date for name. Make alterations to myExt if running 2007
or 2010.........xlsx or xlsm and also change Len(currfile) -4 to -5


Sub namebooks_increment()
' currfile - 2012-1-3.xls
' currfile - 2012-1-3(1).xls
' currfile - 2012-1-3(2).xls
Dim myPath As String, myFile As String, myExt As String
Dim mySerial, currfile As String
currfile = ActiveWorkbook.Name
mySerial = ""
myPath = "C:\Gordstuff\"
myFile = Left(currfile, Len(currfile) - 4) _
& " - " & Format(Date, "YYYY-MM-DD")
myExt = ".xls"

' create output using sequence 1 to n if file already exists
If Len(Dir(myPath & myFile & mySerial & myExt)) > 0 Then

Do While Len(Dir(myPath & myFile & mySerial & myExt)) > 0
mySerial = "(" & Val(Mid(mySerial, 2)) + 1 & ")"
Loop

End If

'save a backup
ActiveWorkbook.SaveCopyAs Filename:=myPath & myFile & _
mySerial & myExt

'save current workbook
ActiveWorkbook.Save

End Sub



Gord

'Don Guillett[_2_ Wrote:
;992007']This is all you really need. It will save any file to the same
folder
but a subfolder named backup (even if it doesn't exist). You really
don't need the date, etc. You could put this in the Thisworkbook
module under workbook_open and/or assign to a button or shape

Sub Backup()
On Error GoTo BackupFile
MkDir CurDir & "\Backup"
BackupFile:
With ActiveWorkbook
MyWB = .Path & "\BACKUP\" & .Name
.SaveCopyAs MyWB
.Save
End With
End Sub



Don, thank you for your help. I'm a little confused though. I put the
code in as you directed, saved, closed, opened & closed the document.
Went to find the backup but I am not seeing any type of Backup folder. I
did find a backup copy, and I suppose I could just move the copy over to
the folder, but I do want to keep the last 7 backups - that way I can
tell where/how/when a mistake was made if I need to do so. Will this
method name the backup copies consecutively or will it write over the
last one?