Total number of seconds since computer was started

What function should I use to get the total number of seconds since
computer started (or similar).

Since midnight is not good (unless since midnight before computer was
started.

Thanks
H. Martins

Jay
Tue Oct 23 06:44:03 PDT 2007

There is nothing built into VBA that can do this. After Googling a bit, all
I found was that the command-line version of SystemInfo.exe will display the
"Up Time" mixed in with a lot of other data, which you can filter with the
DOS "find" command. This will show days/hours/minutes/seconds, not just
seconds, but that may be OK for what you're doing.

Sub UpTime()
Const qot = """"
Shell "cmd /k systeminfo | find " _
& qot & "Up Time" & qot, vbNormalFocus
End Sub

If you want to use the time for further processing in your macro, you'll
have to pipe the output of the command line into a temporary text file, read
that file into the macro, and perhaps parse the numbers into whatever form
you need.

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

H. Martins wrote:
> What function should I use to get the total number of seconds since
> computer started (or similar).
>
> Since midnight is not good (unless since midnight before computer was
> started.
>
> Thanks
> H. Martins

Helmut
Tue Oct 23 08:06:05 PDT 2007

Hi Jay,

>Sub UpTime()
> Const qot = """"
> Shell "cmd /k systeminfo | find " _
> & qot & "Up Time" & qot, vbNormalFocus
>End Sub

that was a very valuable suggestion.

All together, so far, without parsing the numbers:

Option Explicit
' -----------------------------------------------------
' all the complicated stuff
' from Jost Schwider, http://vb-tec.de/xshell.htm
' there are other sources as well
' -----------------------------------------------------
Private Declare Function CloseHandle Lib "kernel32" ( _
ByVal hObject As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" ( _
ByVal hProcess As Long, lpExitCode As Long) As Long
Private Declare Function OpenProcess Lib "kernel32" ( _
ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long

' --------------------------------------------------------------

Public Function ShellX( _
ByVal PathName As String, _
Optional ByVal WindowStyle As VbAppWinStyle = vbMinimizedFocus, _
Optional ByVal Events As Boolean = True _
) As Long

'declarations:
Const STILL_ACTIVE = &H103&
Const PROCESS_QUERY_INFORMATION = &H400&
Dim ProcId As Long
Dim ProcHnd As Long

'get Process-Handle
ProcId = Shell(PathName, WindowStyle)
ProcHnd = OpenProcess(PROCESS_QUERY_INFORMATION, True, ProcId)

'Wait for process-end
Do
If Events Then DoEvents
GetExitCodeProcess ProcHnd, ShellX
Loop While ShellX = STILL_ACTIVE

'Aufräumen:
CloseHandle ProcHnd
End Function

' --------------------------------------------
Sub Macro6()
Dim s As String
Dim v As Variant
v = ShellX("cmd /C systeminfo > c:\test\system.txt")

Open "c:\test\system.txt" For Input As #1
While EOF(1) = False
Line Input #1, s
If InStr(s, "Up Time") Then
MsgBox s
GoTo myexit
End If
Wend
myexit:
Close #1
End Sub

By the way, I can't get "do loop" to work
instead of "while" plus "Goto".
Any suggestions on that?

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Helmut
Tue Oct 23 09:40:20 PDT 2007

Hi everybody,

we all like to play with code...
I've arrived at that:

Option Explicit
' ------------------------------------------------
Private Declare Function CloseHandle Lib "kernel32" ( _
ByVal hObject As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" ( _
ByVal hProcess As Long, lpExitCode As Long) As Long
Private Declare Function OpenProcess Lib "kernel32" ( _
ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long
' ------------------------------------------------------

Public Function ShellX( _
ByVal PathName As String, _
Optional ByVal WindowStyle As VbAppWinStyle = vbMinimizedFocus, _
Optional ByVal Events As Boolean = True _
) As Long

'declarations:
Const STILL_ACTIVE = &H103&
Const PROCESS_QUERY_INFORMATION = &H400&
Dim ProcId As Long
Dim ProcHnd As Long

'Get process-handle:
ProcId = Shell(PathName, WindowStyle)
ProcHnd = OpenProcess(PROCESS_QUERY_INFORMATION, True, ProcId)

'Wait for prozess-end:
Do
If Events Then DoEvents
GetExitCodeProcess ProcHnd, ShellX
Loop While ShellX = STILL_ACTIVE

'clean up
CloseHandle ProcHnd

End Function
Sub Macro6TripleA()

Dim strA() As String
Dim sTmp As String
Dim vVar As Variant
Dim lTmp As Long
Dim lSec As Long
Dim z As Long
vVar = ShellX("cmd /C systeminfo > c:\test\system.txt")

Open "c:\test\system.txt" For Input As #1
While EOF(1) = False
Line Input #1, sTmp
If InStr(sTmp, "Up Time") Then
' MsgBox sTmp
strA() = Split(sTmp, " ")
GoTo myexit
End If
Wend
myexit:
Close #1
lSec = 0
For lTmp = 0 To UBound(strA) - 1
If IsNumeric(strA(lTmp)) Then
z = z + 1
Select Case z
Case 1: lSec = lSec + CLng(strA(lTmp)) * 86400
Case 2: lSec = lSec + CLng(strA(lTmp)) * 3600
Case 3: lSec = lSec + CLng(strA(lTmp)) * 60
Case 4: lSec = lSec + CLng(strA(lTmp))
End Select
End If
Next
MsgBox "active for " & CStr(lSec) & " seconds"
End Sub

I've split the string from systeminfo using spaces,
checked whether there were a numerical results in the array,
assuming that there are no such results
but days, hours, minutes and seconds
and added them all.

Have some fun.

Credits to Jost Schwider, http://vb-tec.de/xshell.htm

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

H
Tue Oct 23 09:58:31 PDT 2007


Oooops

I don't dare to try that much code. I would stall.

I was wondering if there was a 'simple' function for that ...

What about converting today's date and time to long, or double
representing minutes or seconds?

H. Martins

Helmut
Tue Oct 23 10:34:15 PDT 2007

MsgBox Timer

Seconds from midnight

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

H
Tue Oct 23 10:49:45 PDT 2007

On Oct 23, 6:34 pm, Helmut Weber <nbhymsjxd...@mailinator.com> wrote:
> MsgBox Timer
>
> Seconds from midnight

The problem is that Timer is unsuitable for use as a countdown timer
across midnight.

H. Martins

Jay
Tue Oct 23 12:36:29 PDT 2007

H. Martins wrote:
> Oooops
>
> I don't dare to try that much code. I would stall.
>
> I was wondering if there was a 'simple' function for that ...
>
> What about converting today's date and time to long, or double
> representing minutes or seconds?
>
> H. Martins

The Now function returns the current system date and time as a Date data
type. Under the hood, a Date value is a Variant containing a decimal number
representing the number of hours (and fractions of hours) since midnight on
1 January 1900. So if you write

Sub demo1()
Dim days As Double
days = CDbl(Now)
MsgBox days & " days since 1/1/1900"
End Sub

the result for sometime today would be 39378.6319791667. The integer part of
that is the number of whole days that have elapsed since midnight on 1
January 1900. (Strictly speaking, only the first three digits of the
fractional part are significant; the rest comes from representation errors
in converting binary to decimal.)

Similarly, the Time function returns the current system time as a Date data
type, and as a Double it's just the fractional part of the Now value,
representing the fraction of a day since midnight. To get minutes and
seconds,

Sub demo2()
Dim days As Double
Dim mins As Integer, secs As Integer
days = CDbl(Time)
mins = days * 24 * 60 ' note integer cooercion
secs = ((days * 24# * 60#) - mins) * 60
MsgBox mins & ":" & secs
End Sub

The problem with both of these is that you have no way inside VBA of finding
the time the system was last started. That means you don't know the up time.
I suspect that either the startup time or the number of seconds since
startup is stored somewhere in the registry -- after all, the Sysinfo
program must be able to get it from somewhere -- but I don't know where it
is.

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

Jay
Tue Oct 23 12:42:42 PDT 2007

Slip of the fingers -- Now is represented as the number of _days_ and
fractions of _days_ since 1 Jan 1900.

Jay Freedman wrote:
> H. Martins wrote:
>> Oooops
>>
>> I don't dare to try that much code. I would stall.
>>
>> I was wondering if there was a 'simple' function for that ...
>>
>> What about converting today's date and time to long, or double
>> representing minutes or seconds?
>>
>> H. Martins
>
> The Now function returns the current system date and time as a Date
> data type. Under the hood, a Date value is a Variant containing a
> decimal number representing the number of hours (and fractions of
> hours) since midnight on 1 January 1900. So if you write
>
> Sub demo1()
> Dim days As Double
> days = CDbl(Now)
> MsgBox days & " days since 1/1/1900"
> End Sub
>
> the result for sometime today would be 39378.6319791667. The integer
> part of that is the number of whole days that have elapsed since
> midnight on 1 January 1900. (Strictly speaking, only the first three
> digits of the fractional part are significant; the rest comes from
> representation errors in converting binary to decimal.)
>
> Similarly, the Time function returns the current system time as a
> Date data type, and as a Double it's just the fractional part of the
> Now value, representing the fraction of a day since midnight. To get
> minutes and seconds,
>
> Sub demo2()
> Dim days As Double
> Dim mins As Integer, secs As Integer
> days = CDbl(Time)
> mins = days * 24 * 60 ' note integer cooercion
> secs = ((days * 24# * 60#) - mins) * 60
> MsgBox mins & ":" & secs
> End Sub
>
> The problem with both of these is that you have no way inside VBA of
> finding the time the system was last started. That means you don't
> know the up time. I suspect that either the startup time or the
> number of seconds since startup is stored somewhere in the registry
> -- after all, the Sysinfo program must be able to get it from
> somewhere -- but I don't know where it is.

Karl
Tue Oct 23 17:15:37 PDT 2007

H. Martins <HJRMartins@gmail.com> wrote:
> What function should I use to get the total number of seconds since
> computer started (or similar).

If the computer's been up for less than ~43 days, the GetTickCount API would be the
ticket. Of course, that's a hefty *if* there.
--
.NET: It's About Trust!
http://vfred.mvps.org

Jay
Tue Oct 23 19:57:10 PDT 2007

On Tue, 23 Oct 2007 17:15:37 -0700, "Karl E. Peterson" <karl@mvps.org>
wrote:

>H. Martins <HJRMartins@gmail.com> wrote:
>> What function should I use to get the total number of seconds since
>> computer started (or similar).
>
>If the computer's been up for less than ~43 days, the GetTickCount API would be the
>ticket. Of course, that's a hefty *if* there.

Hi Karl,

I figured if it was in there somewhere, you'd know about it. :-)

The GetTickCount function returns the number of milliseconds since
startup. (According to the documentation at
http://msdn2.microsoft.com/en-us/library/ms724408.aspx, it wraps
around to 0 after 49.7 days.) Here's a VBA sample:

Private Declare Function Win32GetTickCounter Lib "Kernel32" _
Alias "GetTickCount" () As Long

Sub demo3()
Dim TickValue As Long
Dim ComputerHours As Long
Dim ComputerMinutes As Long
Dim ComputerSeconds As Long

TickValue = Win32GetTickCounter

ComputerHours = (TickValue \ 3596400) Mod 24
ComputerMinutes = (TickValue \ 59940) Mod 60
ComputerSeconds = (TickValue \ 999) Mod 60

MsgBox "This computer has been on for " & _
CStr(ComputerHours) & " hours, " & _
CStr(ComputerMinutes) & " minutes, " & _
CStr(ComputerSeconds) & " seconds"
End Sub

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.

HelmutWeber
Tue Oct 23 23:52:01 PDT 2007

Hi Martins,

> > MsgBox Timer
> >
> > Seconds from midnight
>
> The problem is that Timer is unsuitable for use as a countdown timer
> across midnight.

sure, I unfortunately hit the send-button too early, sorry.

--
Greetings from Bavaria, Germany
Helmut Weber, MVP WordVBA
"red.sys" & chr(64) & "t-online.de"
Word 2002, Windows 2000 (german versions)

Tony
Wed Oct 24 01:01:26 PDT 2007

Vista also has GetTickCount64 which, I guess, wraps eventually.

--
Enjoy,
Tony

"Karl E. Peterson" <karl@mvps.org> wrote in message
news:ufV4BMdFIHA.4400@TK2MSFTNGP04.phx.gbl...
> H. Martins <HJRMartins@gmail.com> wrote:
>> What function should I use to get the total number of seconds since
>> computer started (or similar).
>
> If the computer's been up for less than ~43 days, the GetTickCount API
> would be the ticket. Of course, that's a hefty *if* there.
> --
> .NET: It's About Trust!
> http://vfred.mvps.org
>

Karl
Wed Oct 24 17:15:21 PDT 2007

Jay Freedman <jay.freedman@verizon.net> wrote:
> On Tue, 23 Oct 2007 17:15:37 -0700, "Karl E. Peterson" <karl@mvps.org>
> wrote:
>
>> H. Martins <HJRMartins@gmail.com> wrote:
>>> What function should I use to get the total number of seconds since
>>> computer started (or similar).
>>
>> If the computer's been up for less than ~43 days, the GetTickCount API would be
>> the ticket. Of course, that's a hefty *if* there.
>
> I figured if it was in there somewhere, you'd know about it. :-)
>
> The GetTickCount function returns the number of milliseconds since
> startup. (According to the documentation at
> http://msdn2.microsoft.com/en-us/library/ms724408.aspx, it wraps
> around to 0 after 49.7 days.)

Ah, 49 days. Sounds more likely, yeah. But there's a catch there. <G> Hint:

?49.7 * 24 * 60 * 60 * 1000, 2^32
4294080000 4294967296

In VB, the return value from GetTickCount "goes negative" at 2^31, which means you
need to twiddle that signbit (add 2^32 if retval < 0) if you want it to make sense.

Another option, although *far* more complicated for nearly any purpose other than
curiosity, would be to query the registry like the Uptime utility does.

Uptime.exe Tool Allows You to Estimate Server Availability with Windows NT 4.0
SP4 or Higher
http://support.microsoft.com/kb/232243

> Here's a VBA sample:
>
> Private Declare Function Win32GetTickCounter Lib "Kernel32" _
> Alias "GetTickCount" () As Long
>
> Sub demo3()
> Dim TickValue As Long
> Dim ComputerHours As Long
> Dim ComputerMinutes As Long
> Dim ComputerSeconds As Long
>
> TickValue = Win32GetTickCounter
>
> ComputerHours = (TickValue \ 3596400) Mod 24
> ComputerMinutes = (TickValue \ 59940) Mod 60
> ComputerSeconds = (TickValue \ 999) Mod 60
>
> MsgBox "This computer has been on for " & _
> CStr(ComputerHours) & " hours, " & _
> CStr(ComputerMinutes) & " minutes, " & _
> CStr(ComputerSeconds) & " seconds"
> End Sub

Where'd you get those numbers? That doesn't seem to be getting me where I want to
go. Here's how I typically break down milliseconds into H:MM:SS...

Public Function FormatHMS(ByVal Milliseconds As Long, Optional ShowDecimal As
Boolean) As String
Dim Rtn As String
Dim h As Long, m As Long, s As Long, d As Long

h = Milliseconds \ 3600000 'hours
m = (Milliseconds \ 60000) Mod 60 'minutes
s = (Milliseconds \ 1000&) Mod 60 'seconds
d = Milliseconds Mod 1000& 'factional seconds

Rtn = Format$(h, "00") & ":" & _
Format$(m, "00") & ":" & _
Format$(s, "00")
If ShowDecimal Then
Rtn = Rtn & "." & Format$(d, "000")
End If
FormatHMS = Rtn
End Function

Thanks... Karl
--
.NET: It's About Trust!
http://vfred.mvps.org

Jay
Wed Oct 24 19:44:51 PDT 2007

On Wed, 24 Oct 2007 17:15:21 -0700, "Karl E. Peterson" <karl@mvps.org>
wrote:

>Jay Freedman <jay.freedman@verizon.net> wrote:
>> On Tue, 23 Oct 2007 17:15:37 -0700, "Karl E. Peterson" <karl@mvps.org>
>> wrote:
>>
>>> H. Martins <HJRMartins@gmail.com> wrote:
>>>> What function should I use to get the total number of seconds since
>>>> computer started (or similar).
>>>
>>> If the computer's been up for less than ~43 days, the GetTickCount API would be
>>> the ticket. Of course, that's a hefty *if* there.
>>
>> I figured if it was in there somewhere, you'd know about it. :-)
>>
>> The GetTickCount function returns the number of milliseconds since
>> startup. (According to the documentation at
>> http://msdn2.microsoft.com/en-us/library/ms724408.aspx, it wraps
>> around to 0 after 49.7 days.)
>
>Ah, 49 days. Sounds more likely, yeah. But there's a catch there. <G> Hint:
>
> ?49.7 * 24 * 60 * 60 * 1000, 2^32
> 4294080000 4294967296
>
>In VB, the return value from GetTickCount "goes negative" at 2^31, which means you
>need to twiddle that signbit (add 2^32 if retval < 0) if you want it to make sense.
>
>Another option, although *far* more complicated for nearly any purpose other than
>curiosity, would be to query the registry like the Uptime utility does.
>
> Uptime.exe Tool Allows You to Estimate Server Availability with Windows NT 4.0
>SP4 or Higher
> http://support.microsoft.com/kb/232243
>

Actually, according to that page, Uptime.exe "depends on the Event log
for the data it uses to calculate availability." Another page explains
that "although you use the registry to collect performance data, the
data is not stored in the registry database. Instead, calling the
registry functions with the HKEY_PEFORMANCE_DATA key causes the system
to collect the data from the appropriate system object managers."
Apparently you call the RegQueryValueEx function and that in turn asks
the EventLog service for the value. Yeah, it's probably too much work.

>> Here's a VBA sample:
>>
>> Private Declare Function Win32GetTickCounter Lib "Kernel32" _
>> Alias "GetTickCount" () As Long
>>
>> Sub demo3()
>> Dim TickValue As Long
>> Dim ComputerHours As Long
>> Dim ComputerMinutes As Long
>> Dim ComputerSeconds As Long
>>
>> TickValue = Win32GetTickCounter
>>
>> ComputerHours = (TickValue \ 3596400) Mod 24
>> ComputerMinutes = (TickValue \ 59940) Mod 60
>> ComputerSeconds = (TickValue \ 999) Mod 60
>>
>> MsgBox "This computer has been on for " & _
>> CStr(ComputerHours) & " hours, " & _
>> CStr(ComputerMinutes) & " minutes, " & _
>> CStr(ComputerSeconds) & " seconds"
>> End Sub
>
>Where'd you get those numbers? That doesn't seem to be getting me where I want to
>go. Here's how I typically break down milliseconds into H:MM:SS...
>

Where does anyone get weird numbers? I copied something off the web
without thinking about it too hard. ;-) It looks like 3596400 =
3600000 - 600, and something similar for the others. It might even
work...

> Public Function FormatHMS(ByVal Milliseconds As Long, Optional ShowDecimal As
>Boolean) As String
> Dim Rtn As String
> Dim h As Long, m As Long, s As Long, d As Long
>
> h = Milliseconds \ 3600000 'hours
> m = (Milliseconds \ 60000) Mod 60 'minutes
> s = (Milliseconds \ 1000&) Mod 60 'seconds
> d = Milliseconds Mod 1000& 'factional seconds
>
> Rtn = Format$(h, "00") & ":" & _
> Format$(m, "00") & ":" & _
> Format$(s, "00")
> If ShowDecimal Then
> Rtn = Rtn & "." & Format$(d, "000")
> End If
> FormatHMS = Rtn
> End Function
>
>Thanks... Karl

Yeah, I'd go with that.

Thanks to you, too.

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.

Karl
Thu Oct 25 10:06:58 PDT 2007

Jay Freedman <jay.freedman@verizon.net> wrote:
>> Another option, although *far* more complicated for nearly any purpose other than
>> curiosity, would be to query the registry like the Uptime utility does.
>>
>> Uptime.exe Tool Allows You to Estimate Server Availability with Windows NT 4.0
>> SP4 or Higher
>> http://support.microsoft.com/kb/232243
>
> Actually, according to that page, Uptime.exe "depends on the Event log
> for the data it uses to calculate availability." Another page explains
> that "although you use the registry to collect performance data, the
> data is not stored in the registry database. Instead, calling the
> registry functions with the HKEY_PEFORMANCE_DATA key causes the system
> to collect the data from the appropriate system object managers."

Sorry, yeah, I knew it was the event log, and for some reason just typed registry.

> Apparently you call the RegQueryValueEx function and that in turn asks
> the EventLog service for the value. Yeah, it's probably too much work.

Pretty nasty. There's a purpose, to be sure, and it's almost always fulfilled by
that little utility. I've never encountered an application that really truly needed
it. Not that one might not exist.

>>> Here's a VBA sample:
>>>
>>> Private Declare Function Win32GetTickCounter Lib "Kernel32" _
>>> Alias "GetTickCount" () As Long
>>>
>>> Sub demo3()
>>> Dim TickValue As Long
>>> Dim ComputerHours As Long
>>> Dim ComputerMinutes As Long
>>> Dim ComputerSeconds As Long
>>>
>>> TickValue = Win32GetTickCounter
>>>
>>> ComputerHours = (TickValue \ 3596400) Mod 24
>>> ComputerMinutes = (TickValue \ 59940) Mod 60
>>> ComputerSeconds = (TickValue \ 999) Mod 60
>>>
>>> MsgBox "This computer has been on for " & _
>>> CStr(ComputerHours) & " hours, " & _
>>> CStr(ComputerMinutes) & " minutes, " & _
>>> CStr(ComputerSeconds) & " seconds"
>>> End Sub
>>
>> Where'd you get those numbers? That doesn't seem to be getting me where I want
>> to go. Here's how I typically break down milliseconds into H:MM:SS...
>
> Where does anyone get weird numbers? I copied something off the web
> without thinking about it too hard. ;-)

LOL! Btdt, I'm afraid. <g>

> It looks like 3596400 =
> 3600000 - 600, and something similar for the others. It might even
> work...

Oh, it "works" in the sense that it "doesn't blow," but if you're after a true(r)
translation... <g>

>> Public Function FormatHMS(ByVal Milliseconds As Long, Optional ShowDecimal As
>> Boolean) As String
>> Dim Rtn As String
>> Dim h As Long, m As Long, s As Long, d As Long
>>
>> h = Milliseconds \ 3600000 'hours
>> m = (Milliseconds \ 60000) Mod 60 'minutes
>> s = (Milliseconds \ 1000&) Mod 60 'seconds
>> d = Milliseconds Mod 1000& 'factional seconds
>>
>> Rtn = Format$(h, "00") & ":" & _
>> Format$(m, "00") & ":" & _
>> Format$(s, "00")
>> If ShowDecimal Then
>> Rtn = Rtn & "." & Format$(d, "000")
>> End If
>> FormatHMS = Rtn
>> End Function
>
> Yeah, I'd go with that.
>
> Thanks to you, too.

:-)
--
.NET: It's About Trust!
http://vfred.mvps.org