Sub assistance
in [Programming]
|
Prev: FIFO HELP!
Next: Application.GetOpenFilename
From: Dave Peterson on 1 Jan 2010 09:10 Maybe something like this: Option Explicit Public RunWhen As Double Public Const cRunWhat = "YourSubRoutineNameHere" Sub Auto_Open() Call StartTimer End Sub Sub Auto_Close() Call StopTimer End Sub Sub StartTimer() If Time < TimeSerial(8, 0, 0) Then RunWhen = Date + TimeSerial(8, 0, 0) Else RunWhen = Date + 1 + TimeSerial(8, 0, 0) End If Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _ Schedule:=True End Sub Sub StopTimer() On Error Resume Next Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _ Schedule:=False End Sub Sub YourSubRoutineNameHere() Dim wks As Worksheet Dim IsVisible As Boolean IsVisible = Live.Visible 'make sure Live is visible if it's not Live.Visible = xlSheetVisible Live.Copy _ before:=ThisWorkbook.Sheets(1) Set wks = ActiveSheet 'just copied version of live With wks .Cells.Copy .Cells.PasteSpecial Paste:=xlPasteValues On Error Resume Next .Name = Format(Date, "ddmmm") If Err.Number <> 0 Then Err.Clear MsgBox "Rename failed!" End If On Error GoTo 0 End With Live.Visible = IsVisible ThisWorkbook.Save Call StartTimer End Sub ========= Depending on what you want to do when the workbook opens, this procedure: Sub Auto_Open() Call StartTimer End Sub could be: Sub Auto_Open() Call YourSubRoutineNameHere End Sub Do you want to set the timer and then decide to run it or always run it. Or you could do what I'd do...ask. Sub Auto_Open() Dim resp As Long resp = MsgBox(Prompt:="Yes to run" _ & vbLf & "No to Start Timer" _ & vbLf & "Cancel to do nothing", _ Buttons:=vbYesNoCancel) Select Case resp Case Is = vbYes: Call YourSubRoutineNameHere Case Is = vbNo: Call StartTimer Case Else MsgBox "You're on your own!" End Select End Sub Max wrote: > > Dave, > The file with your earlier sub will be left open throughout on a PC which is > left on 24x7 > How would the complete sub look like? Thanks -- Dave Peterson
From: Max on 1 Jan 2010 15:07 Many thanks, Dave. I'll need to test it out & monitor over several days > Do you want to set the timer and then decide to run it or always run it Yes, thought I'd set it up once, ie install the sub, then click to run it once, then leave it alone (like a sentinel). I'd need to work on the daily output sheets every now and then
From: Dave Peterson on 1 Jan 2010 15:31 I would always turn my pc off when I went home. And there are lots of times I'd have to reboot--not just because of updates coming from MS. Max wrote: > > Many thanks, Dave. I'll need to test it out & monitor over several days > > > Do you want to set the timer and then decide to run it or always run it > Yes, thought I'd set it up once, ie install the sub, then click to run it > once, then leave it alone (like a sentinel). I'd need to work on the daily > output sheets every now and then > -- Dave Peterson
From: Max on 1 Jan 2010 19:53 >I would always turn my pc off when I went home > And there are lots of times I'd have to reboot--not just because of > updates > coming from MS. Given the above circumstances, how could it be practically done then? The process to auto-fire the 1st sub needs to be unmanned. The PC used is a shared, common PC. The only thing that I know is that it's left on round the clock (hence that excel file can also be left open in it). At the time when 1st sub needs to auto-fire, there's nobody around. Grateful for views.
From: Dave Peterson on 1 Jan 2010 20:41
I've never been in a situation where the pc is left on continuously. Even under the best of circumstances, I've had to reboot the pc. The question that you'll have to answer is what happens after the reboot? Will a person be there to launch excel and your workbook? If yes, can he/she be trusted to start your application correctly? If no, then you'll need some sort of scheduling program (windows scheduler(???) or a visit to google) and you'll have to know how to start your program (run and then start timer or start timer in that previous message). I don't have an answer for you--well, I do, but most people wouldn't like it. Find a trusted employee who can be trained to start the pc, start excel and start your program following the rules you want. (I like the human touch <vbg>.) Max wrote: > > >I would always turn my pc off when I went home > > And there are lots of times I'd have to reboot--not just because of > > updates > > coming from MS. > > Given the above circumstances, how could it be practically done then? The > process to auto-fire the 1st sub needs to be unmanned. The PC used is a > shared, common PC. The only thing that I know is that it's left on round the > clock (hence that excel file can also be left open in it). At the time when > 1st sub needs to auto-fire, there's nobody around. Grateful for views. -- Dave Peterson |