Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Archiving
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" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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" 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Archiving
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" wrote in message ... 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Archiving | Excel Discussion (Misc queries) | |||
Archiving the Outline | Excel Discussion (Misc queries) | |||
Archiving .XLS Sheets | Excel Discussion (Misc queries) | |||
Automated Archiving | Excel Discussion (Misc queries) | |||
More Archiving Problems | Excel Worksheet Functions |