Return System Time Zone

[prahz]

I am creating an application that will be used by users in both the US
and India.

There is a cell in my worksheet that I want the Current Date, the
Current Time, and Time Zone of the user to output to.
-For example, in cell A3, I want the macro to output 12/8/06 4:05PM
(CST). I know how to get the current system date and time (using the
Now function), but how do I return the system time zone?

 

[Martin Fishlock]

Excel uses the local time and terefore you need to use system calls to get
the information.

Chip Pearson (http://www.cpearson.com) has a good description with examples
of using time zones at http://www.cpearson.com/excel/timezone.htm

 

[Michel Pierron]

Hi prahz,
If you want a simple function:
Function CurZone() As String
Const Key$ = "HKLM\System\CurrentControlSet\Control\TimeZoneInformation\"
With CreateObject("WScript.Shell")
CurZone = .RegRead(Key & "StandardName") & " (" _
& .RegRead(Key & "ActiveTimeBias") / 60 & " hours for GMT)"
End With
End Function

If you want more details:
Function GMT_Info() As String
Dim CurZone As String
With CreateObject("WScript.Shell")
CurZone = .RegRead("HKLM\System\CurrentControlSet\" _
& "Control\TimeZoneInformation\StandardName")
End With
Const HKLM = &H80000002
Const Computer = "." ' Local machine
Const kPath = "Software\Microsoft\Windows NT\CurrentVersion\Time Zones"
Dim i%, Value$, SubKeys()
With GetObject("winmgmts:{impersonationLevel=impersonate}!\\" _
& Computer & "\root\default:StdRegProv")
..EnumKey HKLM, kPath, SubKeys ' Enum all folders in kPath
For i = 0 To UBound(SubKeys)
..GetStringValue HKLM, kPath & "\" & SubKeys(i), "Std", Value
If Value = CurZone Then
..GetStringValue HKLM, kPath & "\" & SubKeys(i), "Display", Value
GMT_Info = Value
Exit For
End If
Next i
End With
End Function

Regards,
MP

 

[prahz]

Michel,

Thanks. Now the output goes something like this: (GMT -6:00) Central
Time (US & Canada). Is there any code out there that can just make it
output the abbreviation, (i.e. CST for Central Time?).

Thanks.