Automatic Archiving
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" wrote in message
...
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
|