Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run an Excel code every 15 minutes of the clock
Right now I have a routine based on CPearson's OnTime running on my Excel.
But it does not really meet my requirement. It runs every 15 minutes of the clock only if I start Excel before 8 am. If I start my Excel at 10:07, the module will run at 10:22 followed by 10:37, etc. I need my Excel module to run at every 15 minutes of the clock. If I start my excel at 10:07, I want the module to fire up initially at 10.15 followed by 10.30, 10.45, etc. My knowledge of excel programming is rather basic. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run an Excel code every 15 minutes of the clock
Open excel
Run this macro every 50 seconds instead of every 15 min. Dim myres As Integer myres = Mid(Time, 4, 2) Mod 15 If myres = 0 Then MsgBox "DO WHAT U NEED HERE!" End If Best regards, FrÃ* "thomas" wrote: Right now I have a routine based on CPearson's OnTime running on my Excel. But it does not really meet my requirement. It runs every 15 minutes of the clock only if I start Excel before 8 am. If I start my Excel at 10:07, the module will run at 10:22 followed by 10:37, etc. I need my Excel module to run at every 15 minutes of the clock. If I start my excel at 10:07, I want the module to fire up initially at 10.15 followed by 10.30, 10.45, etc. My knowledge of excel programming is rather basic. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run an Excel code every 15 minutes of the clock
Split your code into two parts:
1. Adjust the first OnTime to take you to the next 15 minute point, So if NOW shows 10:07, set the first OnTIme to take you to 10:15. 2. Set the remaining OnTimes for even 15 minute intervals -- Gary''s Student - gsnu200820 "thomas" wrote: Right now I have a routine based on CPearson's OnTime running on my Excel. But it does not really meet my requirement. It runs every 15 minutes of the clock only if I start Excel before 8 am. If I start my Excel at 10:07, the module will run at 10:22 followed by 10:37, etc. I need my Excel module to run at every 15 minutes of the clock. If I start my excel at 10:07, I want the module to fire up initially at 10.15 followed by 10.30, 10.45, etc. My knowledge of excel programming is rather basic. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run an Excel code every 15 minutes of the clock
Private mdtNextOnTime as Date
Dim d as Date d = Now mdtNextOnTime = Int(d) + TimeSerial(Hour(d), (Minute(d) \ 15 + 1) * 15, 0) Regards, Peter T "thomas" wrote in message ... Right now I have a routine based on CPearson's OnTime running on my Excel. But it does not really meet my requirement. It runs every 15 minutes of the clock only if I start Excel before 8 am. If I start my Excel at 10:07, the module will run at 10:22 followed by 10:37, etc. I need my Excel module to run at every 15 minutes of the clock. If I start my excel at 10:07, I want the module to fire up initially at 10.15 followed by 10.30, 10.45, etc. My knowledge of excel programming is rather basic. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run an Excel code every 15 minutes of the clock
I guess if next qtr hour is only a few seconds or minutes later that might
be too soon for the first OnTime macro. Following adds another 15 minutes if the next qtr hour is within 7 minutes Dim d As Date Dim m As Long d = Now mdtNextOnTime = Int(d) + TimeSerial(Hour(d), (Minute(d) \ 15 + 1) * 15, 0) m = Minute(mdtNextOnTime ) - Minute(d) If m < 7 Then mdtNextOnTime = mdtNextOnTime + TimeSerial(0, 15, 0) End If Use the above for the first Ontime macro and for each subsequent OnTime macro Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Private mdtNextOnTime as Date Dim d as Date d = Now mdtNextOnTime = Int(d) + TimeSerial(Hour(d), (Minute(d) \ 15 + 1) * 15, 0) Regards, Peter T "thomas" wrote in message ... Right now I have a routine based on CPearson's OnTime running on my Excel. But it does not really meet my requirement. It runs every 15 minutes of the clock only if I start Excel before 8 am. If I start my Excel at 10:07, the module will run at 10:22 followed by 10:37, etc. I need my Excel module to run at every 15 minutes of the clock. If I start my excel at 10:07, I want the module to fire up initially at 10.15 followed by 10.30, 10.45, etc. My knowledge of excel programming is rather basic. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run an Excel code every 15 minutes of the clock
Peter,
The "Now" got flagged for "Compile Error: Invalid Outside Procedure". But how do I incorporate your code into mine as follows: Option Explicit Private Declare Function GetProcessVersion Lib "kernel32" ( _ ByVal ProcessID As Long) As Long Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long Private Declare Function CloseHandle Lib "Kernel32.dll" (ByVal Handle As Long) As Long Private Declare Function OpenProcess Lib "Kernel32.dll" (ByVal dwDesiredAccessas As Long, ByVal bInheritHandle As Long, ByVal dwProcId As Long) As Long Private Declare Function EnumProcesses Lib "PSAPI.DLL" (ByRef lpidProcess As Long, ByVal cb As Long, ByRef cbNeeded As Long) As Long Private Declare Function GetModuleFileNameExA Lib "PSAPI.DLL" (ByVal hProcess As Long, ByVal hModule As Long, ByVal ModuleName As String, ByVal nSize As Long) As Long Private Declare Function EnumProcessModules Lib "PSAPI.DLL" (ByVal hProcess As Long, ByRef lphModule As Long, ByVal cb As Long, ByRef cbNeeded As Long) As Long Public RunWhen As Double Public Const cRunIntervalSeconds = 900 Public Const cRunWhat = "Make_SGX_Txt" ' the name of the procedure to run Dim FirstTime As Boolean Sub StartTimer() If FirstTime Then RunWhen = Date + TimeSerial(8, 45, 0) Else If Time TimeSerial(8, 45, 0) And Time <= TimeSerial(12, 30, 0) Or Time TimeSerial(13, 59, 0) And Time < TimeSerial(17, 10, 0) Then RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) End If End If Application.OnTime EarliestTime:=RunWhen, _ Procedu=cRunWhat, Schedule:=True End Sub Sub Make_SGX_Txt() Dim ProcID As Long Dim Version As Long Dim LastErr As Long Dim ThisProcID As Long ProcID = 1092 { what I want to do } Thomas |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run an Excel code every 15 minutes of the clock
Indeed the Now function is "Invalid Outside Procedure", you need to put it
inside a procedure. Eg Sub abc() dim dt as Date dt = Now Msgbox dt End sub For your future reference, The top of a module is the "Declarations" area. This is where API functions are declared, together with module or global level variables and constants. Everything else belongs in a procedure (Sub, Function, etc). It's normally best to store the time the Onime macro scheduled to run in a module level variable is so it can be cancelled if/as necessary, which you can do by calling the OnTime as normal but with the argument Schedule:=False It's not clear when you do / don't want to run the OnTime, gaps (not lunchtime?) the first time and when to cancel it altogether. Try and describe. Regards, Peter T "thomas" wrote in message ... Peter, The "Now" got flagged for "Compile Error: Invalid Outside Procedure". But how do I incorporate your code into mine as follows: Option Explicit Private Declare Function GetProcessVersion Lib "kernel32" ( _ ByVal ProcessID As Long) As Long Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long Private Declare Function CloseHandle Lib "Kernel32.dll" (ByVal Handle As Long) As Long Private Declare Function OpenProcess Lib "Kernel32.dll" (ByVal dwDesiredAccessas As Long, ByVal bInheritHandle As Long, ByVal dwProcId As Long) As Long Private Declare Function EnumProcesses Lib "PSAPI.DLL" (ByRef lpidProcess As Long, ByVal cb As Long, ByRef cbNeeded As Long) As Long Private Declare Function GetModuleFileNameExA Lib "PSAPI.DLL" (ByVal hProcess As Long, ByVal hModule As Long, ByVal ModuleName As String, ByVal nSize As Long) As Long Private Declare Function EnumProcessModules Lib "PSAPI.DLL" (ByVal hProcess As Long, ByRef lphModule As Long, ByVal cb As Long, ByRef cbNeeded As Long) As Long Public RunWhen As Double Public Const cRunIntervalSeconds = 900 Public Const cRunWhat = "Make_SGX_Txt" ' the name of the procedure to run Dim FirstTime As Boolean Sub StartTimer() If FirstTime Then RunWhen = Date + TimeSerial(8, 45, 0) Else If Time TimeSerial(8, 45, 0) And Time <= TimeSerial(12, 30, 0) Or Time TimeSerial(13, 59, 0) And Time < TimeSerial(17, 10, 0) Then RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) End If End If Application.OnTime EarliestTime:=RunWhen, _ Procedu=cRunWhat, Schedule:=True End Sub Sub Make_SGX_Txt() Dim ProcID As Long Dim Version As Long Dim LastErr As Long Dim ThisProcID As Long ProcID = 1092 { what I want to do } Thomas |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run an Excel code every 15 minutes of the clock
Peter,
Thumbs up to you. Thomas "Peter T" wrote: Indeed the Now function is "Invalid Outside Procedure", you need to put it inside a procedure. Eg Sub abc() dim dt as Date dt = Now Msgbox dt End sub For your future reference, The top of a module is the "Declarations" area. This is where API functions are declared, together with module or global level variables and constants. Everything else belongs in a procedure (Sub, Function, etc). It's normally best to store the time the Onime macro scheduled to run in a module level variable is so it can be cancelled if/as necessary, which you can do by calling the OnTime as normal but with the argument Schedule:=False It's not clear when you do / don't want to run the OnTime, gaps (not lunchtime?) the first time and when to cancel it altogether. Try and describe. Regards, Peter T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TIME CLOCK code? | Excel Discussion (Misc queries) | |||
TIME CLOCK CODE | Excel Discussion (Misc queries) | |||
How do I calculate time in excel (clock in and clock out | Excel Discussion (Misc queries) | |||
Change EXCEL Clock to Standard Clock or Military Time | Excel Worksheet Functions | |||
Calculating Total Number of Minutes in a Clock Setting | Excel Discussion (Misc queries) |