Automatic Archiving

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

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
>
>

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
> >
> >
>
>
>

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
> > > >
> > > >
> > >
> > >
> > >
>
>
>