Using Excel to Geocode with Bing Maps Web Services

A lot of folks have to geocode data that they get from various folks in their organization, and it often makes its way in to Excel.  Of course, you can batch geocode using Bing Maps Services, but in some scenarios it is a lot more convenient or reasonable to use Excel.

Wouldn’t it be handy if there was an Excel function like =GEOCODE(<Some Address>)?

Well, here it is.

This uses the Bing Maps REST services and synchronous VBA and XML.  This is similar to asynchronous JavaScript and XML (AJAX) but in this case it would be SVAX.

This code is provided without warranty, etc.

You will need a Bing Maps key (See: Getting a Bing Maps Key), Excel, and an internet connection.

Option 1:  Create your own module

1. Expose the Developer tools (See: Show the Developer Tab)
2. Click the Visual Basic button
3. Insert a Module
4. Paste in this code and modify as you see fit:

Option Explicit

Function GeocodeAddress(address As String, BingMapsKey As String) As String

    Dim oHttpReq As MSXML2.XMLHTTP

    Set oHttpReq = New MSXML2.XMLHTTP

    oHttpReq.Open "get", "https://dev.virtualearth.net/REST/v1/Locations?q=" & address & "&o=xml&key=" & BingMapsKey, "false"

    oHttpReq.send

    If oHttpReq.readyState = 4 Then

        GeocodeAddress = oHttpReq.responseXML.SelectNodes("//Point/Latitude").Item(0).Text & "," & oHttpReq.responseXML.SelectNodes("//Point/Longitude").Item(0).Text

    End If

End Function

Function DriveDistance(From As String, Dest As String, BingMapsKey As String) As String

    Dim oHttpReq As MSXML2.XMLHTTP

    Set oHttpReq = New MSXML2.XMLHTTP

    oHttpReq.Open "get", "https://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=" & From & "&wp.1=" & Dest & "&avoid=minimizeTolls&key=" & BingMapsKey, "false"

    oHttpReq.send

    If oHttpReq.readyState = 4 Then

        DriveDistance = oHttpReq.responseXML.SelectNodes("//TravelDistance").Item(0).Text

    End If

End Function

Function GetStateCountryFromPoint(Lat As String, Lon As String, BingMapsKey As String) As String

    Dim oHttpReq As MSXML2.XMLHTTP

    Set oHttpReq = New MSXML2.XMLHTTP

    oHttpReq.Open "get", "https://dev.virtualearth.net/REST/v1/Locations/" & Lat & "," & Lon & "?o=xml&key=" & BingMapsKey, "false"

    oHttpReq.send

    If oHttpReq.readyState = 4 Then

        If oHttpReq.responseXML.SelectNodes("//Address/AdminDistrict").Length > 0 Then

            GetStateCountryFromPoint = oHttpReq.responseXML.SelectNodes("//Address/AdminDistrict").Item(0).Text & ", " & oHttpReq.responseXML.SelectNodes("//Address/CountryRegion").Item(0).Text

        Else

            GetStateCountryFromPoint = "No Country"

        End If

    End If

End Function

Now you can use cell references or addresses to geocode your addresses or find the distance between two locations.

E.g. =GeocodeAddress(“San Antonio, TX”, BingMapsKey)

Option 2:   Add this file to your Add-ins directory.

(See: Office.com: Add or remove add-ins )

This also includes several other geocoding functions.