If you downloaded the example file I posted earlier on today, as well as a few updates to the simple versions of the Gmaps and geolocation functions for Excel, you may have noticed that one of the places on the clickable list of locations was “My location”.

This works using Wikimedia’s geoiplookup service. When I visit http://geoiplookup.wikimedia.org I get the following:

Geo = {"city":"Erith","country":"GB","lat":"51.483299","lon":"0.200000",
"IP":"188.223.229.118","netmask":"24"}

This code module downloads the response from Wikimedia and parses it into an array.

Function GeoIP()
' Developed by Jamie Bull at oCoCarbon - www.ococarbon.com
' Uses geoiplookup.wikimedia.org to get the location of the local IP address
' Requires a reference to Microsoft XML, v6.0
Dim myRequest As XMLHTTP60
Dim Json As String
Dim tmpArray1 As Variant
Dim tmpArray2 As Variant
Dim i, n As Long
    On Error GoTo exitroute
    Set myRequest = New XMLHTTP60
    ' Get Json response
    With myRequest
        .Open "GET", "http://geoiplookup.wikimedia.org"
        .Send
    End With
    Json = myRequest.responseText
    ' Parse Json
    n = InStr(Json, "{") + 1
    Json = Mid(Json, n, Len(Json) - n)
    tmpArray1 = Split(Json, ",")
    ReDim tmpArray2(UBound(tmpArray1), 1)
    For i = 0 To UBound(tmpArray1)
        tmpArray2(i, 1) = Replace(Split(tmpArray1(i), ":")(1), """", "")
        tmpArray2(i, 0) = Replace(Split(tmpArray1(i), ":")(0), """", "")
    Next
    GeoIP = tmpArray2
exitroute:
    Set myRequest = Nothing
End Function

We can then query that array with a number of UDFs:

=My_City() returns the city.
=My_Country() returns the country.
=My_Location() returns the city and country.

=My_Lat() returns the latitude.
=My_Lng() returns the longitude.
=My_LatLng() returns the latitude and longitude.

=My_IP() returns the IP address.

' UDFs to return values
Function My_City()
    My_City = GeoIP(0, 1)
End Function

Function My_Country()
    My_Country = GeoIP(1, 1)
End Function

Function My_Location()
    My_Location = GeoIP(0, 1) & ", " & GeoIP(1, 1)
End Function

Function My_Lat()
    My_Country = GeoIP(2, 1)
End Function

Function My_Lng()
    My_Country = GeoIP(3, 1)
End Function

Function My_LatLng()
    My_Country = GeoIP(2, 1) & ", " & GeoIP(3, 1)
End Function

Function My_IP()
    My_IP GeoIP(4, 1)
End Function

The result from this is not particularly accurate. It will locate your general area reliably, unless you’re accessing the internet from behind something that masks your real IP address, but it won’t give you a precise location.

If I can figure out how to get the results of the code on this page here into Excel then I’ll make an update. This is using local wifi signals to locate your machine, just like your smartphone does. Unfortunately the code only works in Firefox or Chrome. The default browser object in Excel is Internet Explorer so it’s not simple to run.

It’s implementing some code from Samy Kamkar.