Total number of seconds since computer was started

[H. Martins]

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 Freedman]

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
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Helmut Weber]

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 Weber]

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. Martins]

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 Weber]

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. Martins]

MsgBox Timer

Seconds from midnight

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

H. Martins

 

[Jay Freedman]

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
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Jay Freedman]

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

 

[Karl E. Peterson]

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.

 

[Jay Freedman]

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

 

[Helmut Weber]

Hi Martins,

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.

 

[Tony Jollans]

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

 

[Karl E. Peterson]

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

 

[Jay Freedman]

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.

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.

 

[Karl E. Peterson]

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.

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...

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

Yeah, I'd go with that.

Thanks to you, too.