Automatic Archiving

[CLR]

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

 

[Tom Ogilvy]

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

 

[CLR]

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]

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/default.asp?url=/library/en-us/dnofftalk/html/office10032002.asp
Working with Files, Folders and Drives: More VBA Tips and Tricks by David
Shank

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

 

[CLR]

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