Geocoding in Excel

It’s easy to convert addresses into latitudes and longitudes into addresses in Excel. Here’s the Github project with a downloadable Excel file.

This is via Visual Basic code for a GoogleGeocode function that geocodes addresses.

Function GoogleGeocode(address As String) As String
    Dim xDoc As New MSXML2.DOMDocument
    xDoc.async = False
    xDoc.Load ("http://maps.googleapis.com/maps/api/geocode/" + _
        "xml?address=" + address + "&sensor=false")
    If xDoc.parseError.ErrorCode <> 0 Then
        GoogleGeocode = xDoc.parseError.reason
    Else
        xDoc.setProperty "SelectionLanguage", "XPath"
        lat = xDoc.SelectSingleNode("//lat").Text
        lng = xDoc.SelectSingleNode("//lng").Text
        GoogleGeocode = lat & "," & lng
    End If
End Function
  1. Ryan says:

    I find this isn’t working and says, Compile Error; User defined type not defined
    xDoc As New MSXML2.DOMDocument what do I change to fix it?
    Thank you

  2. Richie Lionell says:

    Ryan, Inside the VBE, Go to Tools -> References, then Select Microsoft XML, v6.0 . If that doesn’t work unselect that and select Microsoft XML, v3.0