save twice command

  • Thread starter Thread starter AwesomeSean
  • Start date Start date
A

AwesomeSean

I have a workbook that I want to make a hidden copy in another folder
everytime the file is saved for a backup copy but not overwritting other
backup copies. Is there a way to do this with code? I don't want the person
to exit the current file or rename it, i just want create another copy
somewhere else when the original is saved. Was I clear or did I just confuse
myself?
 
The subroutine below will save a copy of the workbook in the ExcelBak folder
of My Documents everytime a Save command is issued. The file name will be
the same as the original fire will a date stamp added. This was the simplest
way to avoid overwriting but it will fail (that is, overwriting will
happen) if two saves are made with in the same clock minute.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
BackUpName = "C:\Documents and Settings\Owner\My Documents\Excel1Bak\" _
& Replace(ActiveWorkbook.Name, ".xls", _
" " & Format(Now, "yyyy-mm-dd-hh-mm") & ".xls")

ActiveWorkbook.SaveCopyAs BackUpName

End Sub


This is a workbook subrountine. In classical excel right click the Excel
icon to the left of the File item on menu bar and copy and paste the sub to
the ThisWorkbook module

best wishes
 
I usually append the date and time when I want to be sure to have a
unique file name.

Something like this in the workbook module(assuming your running the
code from the workbook you want to backup):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sPath As String
Dim newFile As String

backupFile = thisworkbook.name & " " & Format(Now, "mmddyy hhmmss") &
".xls"
sPath = wherever you want to save it
newFile = sPath & userFile

thisworkbook.SaveAs newFile' your copy

End Sub

Cliff Edwards
 
Sorry, I made a mistake there -

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sPath As String
Dim newFile As String
dim backupfile as string

backupFile = thisworkbook.name & " " & Format(Now, "mmddyy hhmmss") &
".xls"
sPath = wherever you want to save it
newFile = sPath & backupFile


thisworkbook.SaveAs newFile' your copy


End Sub


Cliff Edwards
 
Thanks, Bernard's code works perfect.

I have one thing I would also like to put in. If you open from a template i
made and click save, the save as box pops up and makes you save it under a
new name. What happens is then it saves a copy of the template in the back up
folder. Is there a way to make this not happen? have it know if it is a save
as first?

Thanks for the help. This worked..
 
Or......

Is there a code to make you save it right after you open the template. Like
if you open the template the code checks the file name and if it matches the
template, a save as box pops up for you to save it under a new name.

Thank you very much
 
Bernard, you will not remember me but I remember you from Ft Worth 2005 and
Atlantic City 2007 (I am pretty sure of the year but I lost so much money
there I forgot the year I think). It was great seeing you and Bob Umlas and
John Peltier again.

All I would ask of your code is a reference to a shell object special My
Documents folder makes better sense and automatically create a backup folder
if one does not exist. This "hidden folder" idea does not make a lot of
sense to me. Especially unannounced.

Perhaps a routine to save the most recent 10 to 20 documents suffices ... A
loop to check files by date using filesystemobject File property and delete
the olderst file makes some sense before this "secret" save, using .Delete
or Kill.

Then there is the drawback that this is workbook specific. What if they are
saving the file with a new name, I think the BeforeSave event remembers only
the workbook's current name prior to saving. Not that it probably matters to
the OP. But what if several times in a row the user changes the file's
name - it would be nice to trap if the new file name is different than the
old file name, do this backup count check, otherwise begin from 1.

Making less sense here due to late hour I suppose... But I wanted to say Hi.
I hope to see you in September at TBA Excel Users Conference.

Bill Benson
 
The reason for the "hidden" file was not really to hide it from anyone
because everyone knows where the copy is just in case . We had a problem
where we work off of 1 file the whole week and update it several times daily.
well, last week on the last day, someone put the wrong weeks info in the file
and saved it so we lost everything for the previous week. I wanted to make a
back-up copy in a seperate folder everytime the file was saved just to have
for cases like this. Bernards code worked just how I wanted it too. Thank you
for the help.
 
Back
Top