Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ontime problem; variable not passed from Workbook_Open to StartTim
My ontime program is suppose to run the macro every 15 minutes of the clock
starting from 9.00am to 12.30pm and then from 2.00pm to 4.45pm. In between the two periods, I may quit Excel. Right now, my macro does not actually run the first time at 9.00; it runs at 9.00 only because I open the workbook early and wait for the 15 minutes interval to hit 9.00. My method is mainly cobbled from answer give by Dave in https://www.microsoft.com/office/com...=en-us&m=1&p=1 I've found that by way of msgboxes that the FirsTime "true" is not passed to the module. The firstime in the StartTime module is "false". Please take a look at my code. Because the FirstTime in StartTimer is always false, it can't execute the runwhen timeserial. Private Sub Workbook_Open() FirstTime = True If Time = TimeSerial(9, 0, 0) And Time <= TimeSerial(12, 30, 0) Or Time = TimeSerial(14, 0, 0) Then FirstTime = False End If Call StartTimer FirstTime = False End Sub Option Explicit Private mdtNextOnTime As Date Public RunWhen As Double Public Const cRunWhat = "Make_SGX_Txt" ' the name of the procedure to run Dim FirstTime As Boolean Sub ShellAndWait(ByVal PathName As String, Optional WindowState) Dim hProg As Long Dim hProcess As Long, ExitCode As Long {shellandwait lines} End Sub Sub StartTimer() Dim d As Date, m As Long d = Now mdtNextOnTime = Int(d) + TimeSerial(Hour(d), (Minute(d) \ 15 + 1) * 15, 0) m = Minute(mdtNextOnTime) - Minute(d) If m < 3 Then mdtNextOnTime = mdtNextOnTime + TimeSerial(0, 15, 0) End If If FirstTime Then If Time <= TimeSerial(12, 30, 0) Then RunWhen = Date + TimeSerial(9, 0, 0) Else RunWhen = date + TimeSerial(14, 0, 0) End If Else If Time TimeSerial(8, 44, 0) And Time <= TimeSerial(12, 30, 0) Or Time TimeSerial(13, 44, 0) Then RunWhen = mdtNextOnTime End If End If Application.OnTime EarliestTime:=RunWhen, _ Procedu=cRunWhat, Schedule:=True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ontime problem; variable not passed from Workbook_Open to StartTim
How about adding a test that if it is opened between 9:00 and 9:15, run the
macro and schedule the next at 9:15. -- __________________________________ HTH Bob "thomas" wrote in message ... My ontime program is suppose to run the macro every 15 minutes of the clock starting from 9.00am to 12.30pm and then from 2.00pm to 4.45pm. In between the two periods, I may quit Excel. Right now, my macro does not actually run the first time at 9.00; it runs at 9.00 only because I open the workbook early and wait for the 15 minutes interval to hit 9.00. My method is mainly cobbled from answer give by Dave in https://www.microsoft.com/office/com...=en-us&m=1&p=1 I've found that by way of msgboxes that the FirsTime "true" is not passed to the module. The firstime in the StartTime module is "false". Please take a look at my code. Because the FirstTime in StartTimer is always false, it can't execute the runwhen timeserial. Private Sub Workbook_Open() FirstTime = True If Time = TimeSerial(9, 0, 0) And Time <= TimeSerial(12, 30, 0) Or Time = TimeSerial(14, 0, 0) Then FirstTime = False End If Call StartTimer FirstTime = False End Sub Option Explicit Private mdtNextOnTime As Date Public RunWhen As Double Public Const cRunWhat = "Make_SGX_Txt" ' the name of the procedure to run Dim FirstTime As Boolean Sub ShellAndWait(ByVal PathName As String, Optional WindowState) Dim hProg As Long Dim hProcess As Long, ExitCode As Long {shellandwait lines} End Sub Sub StartTimer() Dim d As Date, m As Long d = Now mdtNextOnTime = Int(d) + TimeSerial(Hour(d), (Minute(d) \ 15 + 1) * 15, 0) m = Minute(mdtNextOnTime) - Minute(d) If m < 3 Then mdtNextOnTime = mdtNextOnTime + TimeSerial(0, 15, 0) End If If FirstTime Then If Time <= TimeSerial(12, 30, 0) Then RunWhen = Date + TimeSerial(9, 0, 0) Else RunWhen = date + TimeSerial(14, 0, 0) End If Else If Time TimeSerial(8, 44, 0) And Time <= TimeSerial(12, 30, 0) Or Time TimeSerial(13, 44, 0) Then RunWhen = mdtNextOnTime End If End If Application.OnTime EarliestTime:=RunWhen, _ Procedu=cRunWhat, Schedule:=True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ontime problem; variable not passed from Workbook_Open to Star
Bob,
Based on the current code, it will run at 9.15. The 15 minute interval is working beautifully. 'MsgBox "FirstTimer is " & FirstTime' placed in the StartTimer before "If FirstTime Then" always return false regardless of the time. "Bob Phillips" wrote: How about adding a test that if it is opened between 9:00 and 9:15, run the macro and schedule the next at 9:15. -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ontime problem; variable not passed from Workbook_Open to Star
What about my suggestion?
-- __________________________________ HTH Bob "thomas" wrote in message ... Bob, Based on the current code, it will run at 9.15. The 15 minute interval is working beautifully. 'MsgBox "FirstTimer is " & FirstTime' placed in the StartTimer before "If FirstTime Then" always return false regardless of the time. "Bob Phillips" wrote: How about adding a test that if it is opened between 9:00 and 9:15, run the macro and schedule the next at 9:15. -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ontime problem; variable not passed from Workbook_Open to Star
Bob,
I suppose you mean to trigger the very first run at 9.15am. I will do that on Monday as it is office computer. Don't leave me. "Bob Phillips" wrote: What about my suggestion? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable passed to database query | Excel Programming | |||
Variable passed to database query | Excel Discussion (Misc queries) | |||
For Next Variable Not Being Passed Through For Second Sheet | Excel Programming | |||
Modify range variable passed through Function | Excel Programming | |||
OnTime event not firing in Workbook_Open event procedure | Excel Programming |