Automatic Archiving

The short answer is to save the information in the workbook - but you seem
to indicate that the users commonly open the workbook and close it without
saving and your criteria is to archive it after 5 of these if they have
elapsed. If so, putting information on the number of openings since the
last save would be fruitless as it would never get saved - and the next
opening would always appear as the first opening since the last save. The
solution then it to write to the registry or write to a text file (commonly
called a log file when used for this purpose). This can be done in the
workbook_open event.

The registry is a poor choice if this is on a network drive and will be
opened by different people or any other situation in which multiple people
will open it with different login ids.

So you could write information to a separate file using low level file io:

http://msdn.microsoft.com/library/de...ce10032002.asp
Working with Files, Folders and Drives: More VBA Tips and Tricks by David
Shank

http://www.applecore99.com/gen/gen029.asp

--
Regards,
Tom Ogilvy

"CLR" <CLR@discussions.microsoft.com> wrote in message
news:018F47A6-5D2D-4675-BF03-8165172B3058@microsoft.com...
> Thank you Sir........your code worked perfectly for what I
asked.......only
> thing is, I asked a little bit wrong<g>........."what the Lieutenant
REALLY
> meant to say", was, when the book opens I want the message box to ask the
> question,(just like it does), and if the user says no, they are permitted
to
> go ahead and use the file at will, but on the fifth open from the most
recent
> save, (or maybe 5 days chronologically, if thats easier) the book must be
> saved before it can be used again.
>
>
> And, thanks for the tip about the SendKeys.....It was for the messagebox
> that stopped the code before, (done several years ago).......I replaced it
> with your suggestion and it works super...
>
> Thanks again,
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
>
> "Tom Ogilvy" wrote:
>
> > If it must be archived and you already know what the name should be, why
> > bother the user. Just archive it.
> >
> > In any event, you can use workbook_Open event and loop until the user
says
> > yes or keep track in the loop and archive it after 5 knows.
> >
> > Private Sub Workbook_Open()
> > for i = 1 to 5
> > ans = msgbox( "Must be archived, do it now?",vbYesNo)
> > if ans = vbYes then exit for
> > Next
> > ' save the workbook
> > End Sub
> >
> > Why are you using sendkeys. If it is because you want to overwrite a
file,
> >
> > Application.DisplayAlerts = False
> > ActiveWorkbook.SaveAs FName
> > Application.DisplayAlerts = True
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "CLR" <CLR@discussions.microsoft.com> wrote in message
> > news:4EBD6796-7844-46F3-BBEF-888BB832BD70@microsoft.com...
> > > Hi All....
> > >
> > > I have a nifty piece of code (aquired from you fine folks) that does
an
> > > excellent job of archiving a critical File that I made. The only
problem
> > is,
> > > that the user never presses the button and cause it to run. My
question
> > is,
> > > is it possible to modify this code so that it will pop-up a message
asking
> > > the user if he wishes to Archive, each time the WorkBook is opened,
and if
> > he
> > > keeps saying NO, then to inform him that if he wishes to proceed
(after
> > say 5
> > > NO's) that the WorkBook MUST be Archived.......or something to that
> > effect.
> > >
> > > Here's the working code.....
> > >
> > > Sub SaveArchive()
> > > ' Saves the workbook to a predetermined Archive Directory and appends
date
> > > and time to filename,
> > > ' then re-configures file so it will naturally be saved to the
directory
> > > from whence it came.
> > >
> > > CurrentPath = CurDir
> > > ArchivePath = "T:\#tools\_ToolRoomArchive\"
> > > WorkBookName = ActiveWorkbook.Name
> > > FName = ArchivePath
> > > FName = FName + Worksheets("All WO's").Range("AH36").Value 'Used
to
> > name
> > > archived file to cell value
> > > FName = FName + Str(Hour(Time)) + "_" + Str(Minute(Time)) + "_" +
> > > Str(Second(Time))
> > > FName = FName + "_" + Str(Month(Date)) + "_" + Str(Day(Date)) +
"_" +
> > > Str(Year(Date))
> > > ActiveWorkbook.SaveAs FName
> > > FName = CurrentPath + "\" + WorkBookName
> > > SendKeys "Y"
> > > ActiveWorkbook.SaveAs FName
> > > End Sub
> > >
> > >
> > > Any help would be much appreciated....
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> >
> >
> >

Ok, I'm getting in 'way over my head here.......I've mucked through your
suggestions to come up with something that works for me in this
situation.....changed the rules again, I know, but thanks to your comments
making me think about things more, I believe this is a better approach to my
immediate problem. The message box now declares that the "Workbook has not
been saved since mm/dd/yyyy......Do it now?"......NO allows the user into the
file without archiving, YES does the archive and resets the date......this
way I give them a reminder they can't forget to archive with each opening
(which they "ought" to do), yet still don't enforce it.....

Here's the code.....
Private Sub Workbook_Open()
Dim MyDate
MyDate = Date
Dim LastDate
LastDate = Range("a6").Value
ans = MsgBox("LOGBOOK has not been Archived since " & LastDate & ".....Do
it now?", vbYesNo)
If ans = vbYes Then
Range("a6").Select
Selection.Value = MyDate
CurrentPath = CurDir
ArchivePath = "c:\ArchiveTest\"
WorkBookName = ActiveWorkbook.Name
FName = ArchivePath
FName = FName + Worksheets("Sheet1").Range("A5").Value
FName = FName + Str(Hour(Time)) + "_" + Str(Minute(Time)) + "_" +
Str(Second(Time))
FName = FName + "_" + Str(Month(Date)) + "_" + Str(Day(Date)) + "_" +
Str(Year(Date))
ActiveWorkbook.SaveAs FName
FName = CurrentPath + "\" + WorkBookName
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FName
Application.DisplayAlerts = True
Else

End If

End Sub


Thanks much for your help Tom, I would never have got there without
it.......both the code and the comments....... I do appreciate!

Vaya con Dios,
Chuck, CABGx3



"Tom Ogilvy" wrote:

> The short answer is to save the information in the workbook - but you seem
> to indicate that the users commonly open the workbook and close it without
> saving and your criteria is to archive it after 5 of these if they have
> elapsed. If so, putting information on the number of openings since the
> last save would be fruitless as it would never get saved - and the next
> opening would always appear as the first opening since the last save. The
> solution then it to write to the registry or write to a text file (commonly
> called a log file when used for this purpose). This can be done in the
> workbook_open event.
>
> The registry is a poor choice if this is on a network drive and will be
> opened by different people or any other situation in which multiple people
> will open it with different login ids.
>
> So you could write information to a separate file using low level file io:
>
> http://msdn.microsoft.com/library/de...ce10032002.asp
> Working with Files, Folders and Drives: More VBA Tips and Tricks by David
> Shank
>
> http://www.applecore99.com/gen/gen029.asp
>
> --
> Regards,
> Tom Ogilvy
>
> "CLR" <CLR@discussions.microsoft.com> wrote in message
> news:018F47A6-5D2D-4675-BF03-8165172B3058@microsoft.com...
> > Thank you Sir........your code worked perfectly for what I
> asked.......only
> > thing is, I asked a little bit wrong<g>........."what the Lieutenant
> REALLY
> > meant to say", was, when the book opens I want the message box to ask the
> > question,(just like it does), and if the user says no, they are permitted
> to
> > go ahead and use the file at will, but on the fifth open from the most
> recent
> > save, (or maybe 5 days chronologically, if thats easier) the book must be
> > saved before it can be used again.
> >
> >
> > And, thanks for the tip about the SendKeys.....It was for the messagebox
> > that stopped the code before, (done several years ago).......I replaced it
> > with your suggestion and it works super...
> >
> > Thanks again,
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > If it must be archived and you already know what the name should be, why
> > > bother the user. Just archive it.
> > >
> > > In any event, you can use workbook_Open event and loop until the user
> says
> > > yes or keep track in the loop and archive it after 5 knows.
> > >
> > > Private Sub Workbook_Open()
> > > for i = 1 to 5
> > > ans = msgbox( "Must be archived, do it now?",vbYesNo)
> > > if ans = vbYes then exit for
> > > Next
> > > ' save the workbook
> > > End Sub
> > >
> > > Why are you using sendkeys. If it is because you want to overwrite a
> file,
> > >
> > > Application.DisplayAlerts = False
> > > ActiveWorkbook.SaveAs FName
> > > Application.DisplayAlerts = True
> > >
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "CLR" <CLR@discussions.microsoft.com> wrote in message
> > > news:4EBD6796-7844-46F3-BBEF-888BB832BD70@microsoft.com...
> > > > Hi All....
> > > >
> > > > I have a nifty piece of code (aquired from you fine folks) that does
> an
> > > > excellent job of archiving a critical File that I made. The only
> problem
> > > is,
> > > > that the user never presses the button and cause it to run. My
> question
> > > is,
> > > > is it possible to modify this code so that it will pop-up a message
> asking
> > > > the user if he wishes to Archive, each time the WorkBook is opened,
> and if
> > > he
> > > > keeps saying NO, then to inform him that if he wishes to proceed
> (after
> > > say 5
> > > > NO's) that the WorkBook MUST be Archived.......or something to that
> > > effect.
> > > >
> > > > Here's the working code.....
> > > >
> > > > Sub SaveArchive()
> > > > ' Saves the workbook to a predetermined Archive Directory and appends
> date
> > > > and time to filename,
> > > > ' then re-configures file so it will naturally be saved to the
> directory
> > > > from whence it came.
> > > >
> > > > CurrentPath = CurDir
> > > > ArchivePath = "T:\#tools\_ToolRoomArchive\"
> > > > WorkBookName = ActiveWorkbook.Name
> > > > FName = ArchivePath
> > > > FName = FName + Worksheets("All WO's").Range("AH36").Value 'Used
> to
> > > name
> > > > archived file to cell value
> > > > FName = FName + Str(Hour(Time)) + "_" + Str(Minute(Time)) + "_" +
> > > > Str(Second(Time))
> > > > FName = FName + "_" + Str(Month(Date)) + "_" + Str(Day(Date)) +
> "_" +
> > > > Str(Year(Date))
> > > > ActiveWorkbook.SaveAs FName
> > > > FName = CurrentPath + "\" + WorkBookName
> > > > SendKeys "Y"
> > > > ActiveWorkbook.SaveAs FName
> > > > End Sub
> > > >
> > > >
> > > > Any help would be much appreciated....
> > > >
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > > >
> > > >
> > >
> > >
> > >
>
>
>