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 code is provided without warranty, etc.
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)
This also includes several other geocoding functions.