Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reset system clock?
I have a procedure that runs from 07:45 to 17:00. The procedure itself
appears to be slowing the system clock in that this computer now shows system time as 14:39, whereas the computer next to it (and unconnected) shows the correct time of 14:44. I know I can reset the system clock by rebooting, is there any way that VBA can be used to reset the clock within another procedure? Or VB or Windows API calls or anything?? Thank you Martin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reset system clock?
In the ThisWorkbook module, try
Private Type SYSTEMTIM wYear As Intege wMonth As Intege wDayOfWeek As Intege wDay As Intege wHour As Intege wMinute As Intege wSecond As Intege wMilliseconds As Intege End Typ Private Declare Function SetSystemTime Lib "kernel32" (lpSystemTime As SYSTEMTIME) As Lon Sub ResetClock( Dim lpSystemTime As SYSTEMTIM lpSystemTime.wDayOfWeek = - lpSystemTime.wDay = 2 lpSystemTime.wYear = 200 lpSystemTime.wMonth = lpSystemTime.wHour = 1 lpSystemTime.wMinute = 3 lpSystemTime.wSecond = lpSystemTime.wMilliseconds = SetSystemTime lpSystemTim End Su |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reset system clock?
Hi
From Help file Time Statement Sets the system time. Syntax Time = time The required time argument is any numeric expression, string expression, or any combination, that can represent a time. Remarks If time is a string, Time attempts to convert it to a time using the time separators you specified for your system. If it can't be converted to a valid time, an error occurs. HTH Ken --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reset system clock?
And the point of hard-coding the time of day when setting the
system clock would be what? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "AA2e72E" wrote in message ... In the ThisWorkbook module, try: Private Type SYSTEMTIME wYear As Integer wMonth As Integer wDayOfWeek As Integer wDay As Integer wHour As Integer wMinute As Integer wSecond As Integer wMilliseconds As Integer End Type Private Declare Function SetSystemTime Lib "kernel32" (lpSystemTime As SYSTEMTIME) As Long Sub ResetClock() Dim lpSystemTime As SYSTEMTIME lpSystemTime.wDayOfWeek = -1 lpSystemTime.wDay = 24 lpSystemTime.wYear = 2004 lpSystemTime.wMonth = 5 lpSystemTime.wHour = 15 lpSystemTime.wMinute = 32 lpSystemTime.wSecond = 0 lpSystemTime.wMilliseconds = 0 SetSystemTime lpSystemTime End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reset system clock?
AFAIK
your code CANT slow the system clock... your code may inadvertently SET it using the DATE or TIME method. keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Martin" wrote: I have a procedure that runs from 07:45 to 17:00. The procedure itself appears to be slowing the system clock in that this computer now shows system time as 14:39, whereas the computer next to it (and unconnected) shows the correct time of 14:44. I know I can reset the system clock by rebooting, is there any way that VBA can be used to reset the clock within another procedure? Or VB or Windows API calls or anything?? Thank you Martin |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reset system clock?
I am using excel to download data from three financial websites once a
minute from each. As I cannot always rely on the "Trade Time" shown on the sites, I use system time ("=Time") to act as a comparison, i.e. if system time <trade time then... , I trade the markets daily and would probably be "in" for only a short period, the fact that the system time appears to slow down over the day when the procedure is running is not much more than an irritant and I can work around it by manually resetting the clock or rebooting. Having looked through Google and http://support.microsoft.com/?kbid=189706 and not getting much further, I wondered if there was a practical way to remove/rectify said irritant. Thank you for all replies, I'll look at it further at the weekend when the markets are closed. Martin Chip Pearson wrote in message ... And the point of hard-coding the time of day when setting the system clock would be what? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "AA2e72E" wrote in message ... In the ThisWorkbook module, try: Private Type SYSTEMTIME wYear As Integer wMonth As Integer wDayOfWeek As Integer wDay As Integer wHour As Integer wMinute As Integer wSecond As Integer wMilliseconds As Integer End Type Private Declare Function SetSystemTime Lib "kernel32" (lpSystemTime As SYSTEMTIME) As Long Sub ResetClock() Dim lpSystemTime As SYSTEMTIME lpSystemTime.wDayOfWeek = -1 lpSystemTime.wDay = 24 lpSystemTime.wYear = 2004 lpSystemTime.wMonth = 5 lpSystemTime.wHour = 15 lpSystemTime.wMinute = 32 lpSystemTime.wSecond = 0 lpSystemTime.wMilliseconds = 0 SetSystemTime lpSystemTime End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reset system clock?
I use to have a vba code that updated the time off the web when desired.
However, for the life of me, I can't seem to find it. It's better to use one of the the downloadable program that can do this directly. However, there is at least one program that I knew of that allowed it to be called from a running program. I can't remember, but see if this is the program from NIST. http://www.boulder.nist.gov/timefreq/service/its.htm Look for the Windows version in the upper right of the screen. Check out help for "Command Line" calls. Two other programs to check on the web are AtomTime, and Atomic Clock. HTH. Dana DeLouis "Martin" wrote in message ... I have a procedure that runs from 07:45 to 17:00. The procedure itself appears to be slowing the system clock in that this computer now shows system time as 14:39, whereas the computer next to it (and unconnected) shows the correct time of 14:44. I know I can reset the system clock by rebooting, is there any way that VBA can be used to reset the clock within another procedure? Or VB or Windows API calls or anything?? Thank you Martin |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reset system clock?
Hi Martin,
If your code is running constantly then Windows may not get enough cycles to do its housekeeping tasks and the slow running clock is the result. There is a DoEvents command that you should run to let Windows catch up. It releases (or stops) your code back to Windows and the code won't continue until Windows has finished doing its thing. You CAN run DoEvents to often. Doing so will slow everything down. Find a spot in your code to run a DoEvents every few minutes. Once a minute would be fine. -- John johnf 202 at hotmail dot com "Martin" wrote in message ... | I have a procedure that runs from 07:45 to 17:00. The procedure itself | appears to be slowing the system clock in that this computer now shows | system time as 14:39, whereas the computer next to it (and unconnected) | shows the correct time of 14:44. | | I know I can reset the system clock by rebooting, is there any way that VBA | can be used to reset the clock within another procedure? Or VB or Windows | API calls or anything?? | | Thank you | Martin | | |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reset system clock?
Thanks Dana
This gives me a couple of options that I had not considered...as I'm currently downloading from three websites, then probably no big deal to do a fourth! The fourth download could then replace my current "system time" and work around quite neatly. The downloads from the site quoted, and AtomTime and worldtimeserver.com (Atomic Clock) appear to allow for scheduled synchronisation of the PC clock and it may be simplest to run this independently. I'll experiment at the weekend. Thanks again. Martin Dana DeLouis wrote in message ... I use to have a vba code that updated the time off the web when desired. However, for the life of me, I can't seem to find it. It's better to use one of the the downloadable program that can do this directly. However, there is at least one program that I knew of that allowed it to be called from a running program. I can't remember, but see if this is the program from NIST. http://www.boulder.nist.gov/timefreq/service/its.htm Look for the Windows version in the upper right of the screen. Check out help for "Command Line" calls. Two other programs to check on the web are AtomTime, and Atomic Clock. HTH. Dana DeLouis |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reset system clock?
Sorry John,
My mistake here. I tried to briefly summarise what I was doing to answer Chip's point. The code is timed to run every 43 seconds to take into account the time taken with the procedures. During the 43 second gap, control is returned to the PC as it would be with "DoEvents" and "DoEvents" does already appear on six occasions within the existing code. Thanks again Martin jaf wrote in message ... Hi Martin, If your code is running constantly then Windows may not get enough cycles to do its housekeeping tasks and the slow running clock is the result. There is a DoEvents command that you should run to let Windows catch up. It releases (or stops) your code back to Windows and the code won't continue until Windows has finished doing its thing. You CAN run DoEvents to often. Doing so will slow everything down. Find a spot in your code to run a DoEvents every few minutes. Once a minute would be fine. -- John johnf 202 at hotmail dot com |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reset system clock?
I keep a shortcut to a .vbs file on my desktop. I stole it from Bill James &
Michael Harris: http://groups.google.com/groups?thre...%40tkmsftngp04 I can just double click on it whenever I'm connected to the internet. And I don't have to load a special workbook in xl to adjust the time. Martin wrote: I have a procedure that runs from 07:45 to 17:00. The procedure itself appears to be slowing the system clock in that this computer now shows system time as 14:39, whereas the computer next to it (and unconnected) shows the correct time of 14:44. I know I can reset the system clock by rebooting, is there any way that VBA can be used to reset the clock within another procedure? Or VB or Windows API calls or anything?? Thank you Martin -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reset system clock?
Dave,
I understand that if you take one file, it could be theft, but if you take several from different sources it's research. From one researcher to another, thank you very much. Martin Dave Peterson wrote in message ... I keep a shortcut to a .vbs file on my desktop. I stole it from Bill James & Michael Harris: http://groups.google.com/groups?thre...%40tkmsftngp04 I can just double click on it whenever I'm connected to the internet. And I don't have to load a special workbook in xl to adjust the time. -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reset system clock?
I don't know enough about regular expressions (except copy|paste <bg) to help.
But those guys did a nice job (and you too with the VBA implementation). Dana DeLouis wrote: Thank you Dave! I learned something new on the .vbs side. :) I did some further "research" also. Here is a quick and dirty modified version of the code in an Excel vba module. Here's what I have so far in case anyone else is interested. Not finalized, or fully tested. I like to use speech, so most others may want to remove that part. There's room for all kinds of neat features and improvements. I kept most of the variables as variants (similar to vbs), and will most likely change them in the future. Thanks again. :) Dana DeLouis -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating a future date using system clock & time remaining | Excel Worksheet Functions | |||
Incremental time values based upon clock in and clock out times | Excel Discussion (Misc queries) | |||
Change EXCEL Clock to Standard Clock or Military Time | Excel Worksheet Functions | |||
Code referencing the system clock? | Excel Programming | |||
Setting the system clock with excel | Excel Programming |