prahz
12/12/2006 11:23:00 PM
Michel, thanks alot, I hope you had that code on already on hand and
didn't do all of that from scratch!
Michel Pierron wrote:
> Hi prahz,
> Without guarantee, but just to please to you:
>
> Function GMT_Info(Optional Abbreviation As Boolean = True) 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
> If Abbreviation Then
> GMT_Info = TZA(SubKeys(i))
> Else
> .GetStringValue HKLM, kPath & "\" & SubKeys(i), "Display", Value
> GMT_Info = Value
> End If
> Exit For
> End If
> Next i
> End With
> End Function
>
> Private Function TZA(ByVal TZI As String) As String
> Select Case TZI
> Case "Dateline Standard Time": TZA = "IDLE"
> Case "Samoa Standard Time": TZA = "MIS"
> Case "Hawaiian Standard Time": TZA = "HAW"
> Case "Alaskan Standard Time": TZA = "ALA"
> Case "Pacific Standard Time": TZA = "PST"
> Case "Mountain Standard Time": TZA = "MST"
> Case "Mexico Standard Time 2": TZA = "MST"
> Case "US Mountain Standard Time": TZA = "MST"
> Case "Canada Central Standard Time": TZA = "CST"
> Case "Mexico Standard Time": TZA = "CST"
> Case "Central Standard Time": TZA = "CST"
> Case "Central America Standard Time": TZA = "CST"
> Case "US Eastern Standard Time": TZA = "EST"
> Case "Eastern Standard Time": TZA = "EST"
> Case "SA Pacific Standard Time": TZA = "EST"
> Case "Pacific SA Standard Time": TZA = "AST"
> Case "Atlantic Standard Time": TZA = "AST"
> Case "SA Western Standard Time": TZA = "AST"
> Case "Newfoundland Standard Time": TZA = "NWF"
> Case "Greenland Standard Time": TZA = "BBA"
> Case "SA Eastern Standard Time": TZA = "BBA"
> Case "E. South America Standard Time": TZA = "BBA"
> Case "Mid-Atlantic Standard Time": TZA = "MAT"
> Case "Azores Standard Time": TZA = "AZO"
> Case "Cape Verde Standard Time": TZA = "AZO"
> Case "GMT Standard Time": TZA = "GMT"
> Case "Greenwich Standard Time": TZA = "GMT"
> Case "W. Central Africa Standard Time": TZA = "AMS"
> Case "W. Europe Standard Time": TZA = "AMS"
> Case "Central Europe Standard Time": TZA = "AMS"
> Case "Romance Standard Time": TZA = "AMS"
> Case "Central European Standard Time": TZA = "AMS"
> Case "GTB Standard Time": TZA = "AIM"
> Case "E. Europe Standard Time": TZA = "BCP"
> Case "South Africa Standard Time": TZA = "BCP"
> Case "Israel Standard Time": TZA = "BCP"
> Case "Egypt Standard Time": TZA = "BCP"
> Case "FLE Standard Time": TZA = "HRI"
> Case "Arabic Standard Time": TZA = "BKR"
> Case "Arab Standard Time": TZA = "BKR"
> Case "Russian Standard Time": TZA = "MSV"
> Case "E. Africa Standard Time": TZA = "BKR"
> Case "Iran Standard Time": TZA = "THE"
> Case "Arabian Standard Time": TZA = "ABT"
> Case "Caucasus Standard Time": TZA = "ABT"
> Case "Afghanistan Standard Time": TZA = "KAB"
> Case "Ekaterinburg Standard Time": TZA = "EIK"
> Case "West Asia Standard Time": TZA = "EIK"
> Case "India Standard Time": TZA = "BCD"
> Case "Nepal Standard Time": TZA = "NPT"
> Case "N. Central Asia Standard Time": TZA = "ADC"
> Case "Central Asia Standard Time": TZA = "ADC"
> Case "Sri Lanka Standard Time": TZA = "ADC"
> Case "Myanmar Standard Time": TZA = "MMT"
> Case "SE Asia Standard Time": TZA = "BHJ"
> Case "North Asia Standard Time": TZA = "BHJ"
> Case "North Asia East Standard Time": TZA = "SST"
> Case "Singapore Standard Time": TZA = "SST"
> Case "China Standard Time": TZA = "SST"
> Case "W. Australia Standard Time": TZA = "SST"
> Case "Taipei Standard Time": TZA = "SST"
> Case "Tokyo Standard Time": TZA = "OST"
> Case "Korea Standard Time": TZA = "SYA"
> Case "Yakutsk Standard Time": TZA = "SYA"
> Case "Cen. Australia Standard Time": TZA = "ADA"
> Case "AUS Central Standard Time": TZA = "ADA"
> Case "E. Australia Standard Time": TZA = "BGP"
> Case "West Pacific Standard Time": TZA = "BGP"
> Case "AUS Eastern Standard Time": TZA = "CMS"
> Case "Tasmania Standard Time": TZA = "HVL"
> Case "Vladivostok Standard Time": TZA = "HVL"
> Case "Central Pacific Standard Time": TZA = "MSN"
> Case "New Zealand Standard Time": TZA = "AWE"
> Case "Fiji Standard Time": TZA = "FKM"
> Case "Tonga Standard Time": TZA = "TOT"
> Case Else: TZA = "???"
> End Select
> End Function
>
> Regards,
> MP
>
> "prahz" <pthomlison@gmail.com> a écrit dans le message de news:
> 1165875594.456817.227880@n67g2000cwd.googlegroups.com...
> > 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?).
> >
> > Date: Sat, Dec 9 2006 7:46 am
> >
> > 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
> >
> >>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?
> >