VBA Function to calculate distance from 2 addresses using MapQuest API or other interfaces

Can you show what you've tried so far with the Mapquest API? Getting to those seems to require joining the community, download/install etc, and you might get better and quicker answers if you can show us the functions you're trying to use.

Try to recreate the issue in a sample database, and upload.

You could roll-your-own calculation.

Example:
1. make a call to Texas A&M geocoding web service
https://geoservices.tamu.edu/Services/Geocode/WebService/GeocoderWebServiceHttpNonParsed_V04_01.aspx?streetAddress=27%20meadhall%20court&city=durham&state=nc&zip=27713&apikey=demo&format=XML&census=false¬Store=false&version=4.01

2. parse the XML results to get the Latitude and Longitude elements.

<WebServiceGeocodeResult version="4.01">
<QueryMetadata>
<TransactionId>d539af81-347e-45e0-8375-3f7080a54026</TransactionId>
<Version>4.01</Version>
<QueryStatusCodeValue>200</QueryStatusCodeValue>
<FeatureMatchingResultType>Success</FeatureMatchingResultType>
<FeatureMatchingResultCount>1</FeatureMatchingResultCount>
<TimeTaken>0.0156006</TimeTaken>
<ExceptionOccured>False</ExceptionOccured>
<Exception/>
<ErrorMessage/>
</QueryMetadata>
<InputAddress>
<StreetAddress>27 MEADHALL CT durham NC 27713</StreetAddress>
<City>durham</City>
<State>NC</State>
<Zip>27713</Zip>
</InputAddress>
<OutputGeocodes>
<OutputGeocode>
<Latitude>35.9189053154933</Latitude>
<Longitude>-78.8949771056968</Longitude>
<NAACCRGISCoordinateQualityCode>03</NAACCRGISCoordinateQualityCode>
<NAACCRGISCoordinateQualityType>StreetSegmentInterpolation</NAACCRGISCoordinateQualityType>
<MatchScore>100</MatchScore>
<MatchType>Exact</MatchType>
<FeatureMatchingResultType>Success</FeatureMatchingResultType>
<FeatureMatchingResultCount>1</FeatureMatchingResultCount>
<FeatureMatchingGeographyType>StreetSegment</FeatureMatchingGeographyType>
<RegionSize>4556.12917160499</RegionSize>
<RegionSizeUnits>Meters</RegionSizeUnits>
<MatchedLocationType>LOCATION_TYPE_STREET_ADDRESS</MatchedLocationType>
<ExceptionOccured>False</ExceptionOccured>
<Exception/>
<ErrorMessage/>
</OutputGeocode>
</OutputGeocodes>
</WebServiceGeocodeResult>

Select allOpen in new window

3. Calculate the distance between the two lat/lon values with the standard formula (it involves trig functions and the circumference of the Earth)

Hi all,

Thanks for responding.

Here is the code I'm using:

Sub GetDistance_MapQuest2(ByVal startFrom As String, ByVal endAt As String, ByRef refMiles As Double, ByRef refHours As Double)


Dim url         As String
Dim resp        As String

Dim req         As MSXML2.XMLHTTP
Dim xdoc        As MSXML2.DOMDocument

url = "http://www.open.mapquestapi.com/directions/v2/route?key=lwCv2rEDHoCHE2VnLG71hKJg0b6a5EA5&drivingStyle=2&callback=renderAdvancedNarrative&ambiguities=ignore&avoidTimedConditions=false&doReverseGeocode=true&outFormat=xml&routeType=fastest&timeType=1&enhancedNarrative=false&shapeFormat=raw&generalize=0&locale=en_US&unit=m"
url = url & "&from=" & Replace(Trim(startFrom), " ", "%20")
url = url & "&to=" & Replace(Trim(endAt), " ", "%20")

Set req = New MSXML2.XMLHTTP
req.Open "GET", url, False
req.send

resp = req.responseText

Set xdoc = req.responseXML

refMiles = CSng(xdoc.selectSingleNode("response / route / distance").Text)
refHours = CLng(xdoc.selectSingleNode("response / route / time").Text) / 60 / 60

End Sub

Sub FindMilesTest()
Dim strStartFrom As String, strEndAt As String, refMiles As Double, refHours As Double

strStartFrom = "1788 East 10th Street, Brooklyn, NY"
strEndAt = "1803 Riverside Drive, New York, NY"

GetDistance_MapQuest2 strStartFrom, strEndAt, refMiles, refHours

Debug.Print refMiles


End Sub

Select allOpen in new window

I know the key is legitimate as I received it yesterday when I signed up with the MapQuest Developer Network. And when I tested it yesterday I got past the send, but it just seemed to have problems reading the xml.

BUT TODAY I'm getting a "system cannot locate the resource specified". (BTW, I was told that once obtained the key would work in a distributed application, so you should be able to try this.)

Any help OR suggestions would be greatly, GREATLY appreciated.

Best,
E

I'm a proponent of late binding.  So, I used CreateObject() rather than adding references to the project

My previous comment was not intended for this thread.

Wow! Works great. I was beginning to disrepair. Thank you. I've been working Access for quite a while, but never had the need to work with API's or XML. May I ask you to explain the XML part. I know it's complicated, but when the docs on an AP say that the Route response field is:

distance: Returns the calculated distance of the route :  <distance>25.150</distance>

for their xml example, is there a way to translate that into how I can get that using vba?

Hope the question makes sense. In any case, thanks so much.

Also, thanks for the geocoding suggestion. I need that as well. But I need actual travel time for my client so a radius calculation wouldn't work. Anyone other suggestions are welcome.

@eantar

Since this is a closed question, please open a new question for your additional question parts.  Please include a link to this question in your new question, then return to this thread and post a link to your new question.

Okay, thanks.

I posted a new followup question. Here's the link https://www.experts-exchange.com/questions/28697473/I-would-like-to-understand-the-xml-portion-returned-in-my-vba-code.html