timezone from lat/longitude

[jimatqsi]

I'm trying to find a way to calculate a timezone from latitude and longitude. Anybody out there doing that in VBA?

Thanks,
Jim

[twinnyfo]

jimatqsi,

Not sure if this would helphttps://developers.google.com/maps/d...tion/timezone/. It is a Google API, and if you don't mind popping up a web browser with your VBA, it might be helpful. My thought is that without an extensive DB of all time zones, we are relegated to using some tools that already exist, but in a different format. Let us know if you come up with anything.

[jimatqsi]

Thanks. Google's the same place I ended up. Should work fine for this app, it's a great service. Here's the code. Doesn't really bring up a browser, it issues a service call and receives a reply within the vba.

    Dim tzName As String
    Dim strSQL As String
    Dim dbs As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim apikey As String
    Dim byteData() As Byte
    Dim XMLHTTP As Object
    Dim TimeZoneID As Long
    Dim GoogleReply As String

Set dbs = CurrentDb

    url = "https://maps.googleapis.com/maps/api/timezone/xml?location=\lat\,\long\&timestamp=\timestamp\&key=\apikey\"
    apikey = "my apikey"
    
    url = Replace(url, "\lat\", Latitude)
    url = Replace(url, "\long\", Longitude)
    url = Replace(url, "\timestamp\", 0)   ' don't need to know what time it is
    url = Replace(url, "\apikey\", apikey)
    
    Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
    
    XMLHTTP.Open "GET", url, False
    
    XMLHTTP.send
    byteData = XMLHTTP.responseBody
    GoogleReply = StrConv(byteData, vbUnicode)
    Set XMLHTTP = Nothing

Thanks again.

[twinnyfo]

That's actually pretty cool. If similar code works for addresses, that could find some applications in sales....

Thanks for sharing!