Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Save copy on Open, keeping old backup copies
Hi y'all,
I put this on Ozgrid yesterday but it's been over 120 views with no 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 to do exactly what I want, but I am just not finding it. Could be I'm blind, but ... I run a log of television & appliance returns. That's all it is, just a log of all the info. Because it has all the info that I need for the returns (serial, customer, when/if the return was filed, etc.) it is vitally important to my job. I've had to start from scratch once, and I don't ever want to have to do that again! The one thing that I've done a few times is change something & save it, and somehow screwed up everything else - then I can't remember what was what & where. I make changes to this log daily, sometimes several times a day. I want the log, titled RA Sheets & Log, saved in C:/Backup/Log Backup-mm/dd immediately upon open. That way, any changes I, or anyone else, make can be easily undone. Ideally, I'd be able to actually have 2 backup folders. One that back up every time it's opened & the other for an automated monthly backup. However, I only actually need the one backup folder, I can make monthly backups myself. I would want the last 7 backups in the folder, anything older could be deleted. In the monthly backup I'd want one every month, like on the 1st or whatever. Any help y'all could give me is very much appreciated. I am a VERY inexperienced VBA & macro user. Since I'm almost 100% certain this solution will consist of either VBA or macros, or both, I better let you know to please tell me exactly where to put stuff. Telling me to insert a module, for example, is just gonna confuse me. lol Again, anything that y'all can do to help is appreciated. Please let me know if you have any questions. Thanks! Ky Last edited by txheart : December 29th 11 at 03:05 PM |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save copy on Open, keeping old backup copies
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 On Dec 29, 8:43*am, txheart wrote: Hi y'all, I put this on Ozgrid yesterday but it's been over 120 views with no 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 to do exactly what I want, but I am just not finding it. Could be I'm blind, but ... I run a log of television & appliance returns. That's all it is, just a log of all the info. Because it has all the info that I need for the returns (serial, customer, when/if the return was filed, etc.) it is vitally important to my job. I've had to start from scratch once, and I don't ever want to have to do that again! The one thing that I've done a few times is change something & save it, and somehow screwed up everything else - then I can't remember what was what & where. I make changes to this log daily, sometimes several times a day. I want the log, titled RA Sheets & Log, saved in C:/Backup/Log Backup-mm/dd immediately upon open. That way, any changes I, or anyone else, make can be easily undone. -Ideally-, I'd be able to actually have 2 backup folders. One that back up every time it's opened & the other for an automated monthly backup. However, I only actually need the one backup folder, I can make monthly backups myself. I would want the last 7 backups in the folder, anything older could be deleted. In the monthly backup I'd want one every month, like on the 1st or whatever. Any help y'all could give me is very much appreciated. I am a VERY inexperienced VBA & macro user. Since I'm almost 100% certain this solution will consist of either VBA or macros, or both, I better let you know to please tell me exactly where to put stuff. Telling me to insert a module, for example, is just gonna confuse me. lol Again, anything that y'all can do to help is appreciated. Please let me know if you have any questions. Thanks! Ky -- txheart |
#3
|
|||
|
|||
Quote:
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save copy on Open, keeping old backup copies
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 On Tue, 3 Jan 2012 19:22:57 +0000, txheart wrote: '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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save copy on Open, keeping old backup copies
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 On Tue, 03 Jan 2012 20:09:31 -0800, Gord Dibben wrote: 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 On Tue, 3 Jan 2012 19:22:57 +0000, txheart wrote: '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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save copy on Open, keeping old backup copies
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 On Tue, 03 Jan 2012 20:23:20 -0800, Gord Dibben wrote: 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 On Tue, 03 Jan 2012 20:09:31 -0800, Gord Dibben wrote: 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 On Tue, 3 Jan 2012 19:22:57 +0000, txheart wrote: '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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto save a backup copy of a workbook | Excel Discussion (Misc queries) | |||
In Excel, I want to create a backup copy whenever I save a file. | Excel Discussion (Misc queries) | |||
save a backup copy in different folder | Excel Discussion (Misc queries) | |||
How do I shut off making a backup copy of a file when I save in E. | Excel Discussion (Misc queries) | |||
How do i save backup copy in a different folder | Excel Discussion (Misc queries) |