Jake,

Very slick. I'd figured out how to pass the address info out to mapquest
using the hyperlink worksheet function but had no idea where to even start
getting the driving distance back out... I clearly will not be leaving my day
job anytime soon...

Thanks a million!

Steve

"Jake Marx" wrote:

Hi Steve,

Here's a function that submits the data for you automatically and retrieves
the distance (if any is returned). I've tested it on several address
combinations - sometimes it works well, and other times it doesn't. If your
addresses are standard addresses (and Mapquest doesn't have to ask for
clarification), it should work.

To get this to work, you must set a reference to "Microsoft XML 6.0" via
Tools | References in the VBE.

Public Function gnDrivingDistance(rsAddress1 As String, _
rsCity1 As String, rsState1 As String, rsZip1 As String, _
rsAddress2 As String, rsCity2 As String, rsState2 As String, _
rsZip2 As String) As Integer
Dim xml As XMLHTTP60
Dim abytPostData() As Byte
Dim sMode As String
Dim sResponse As String
Dim nStartPos As Integer
Dim nEndPos As Integer

abytPostData = StrConv("go=1&do=nw&rmm=1&un=m&cl=en&ct=na&rsres=1 &" & _
"1a=" & rsAddress1 & "&1c=" & rsCity1 & "&1s=" & rsState1 & "&1z=" & _
rsZip1 & "&2a=" & rsAddress2 & "&2c=" & rsCity2 & "&2s=" & rsState2 & _
"&2z=" & rsZip2, vbFromUnicode)

Set xml = New XMLHTTP60
With xml
.Open "POST", _
"http://www.mapquest.com/directions/main.adp"
.setRequestHeader "Content-Type", _
"application/x-www-form-urlencoded"
.send abytPostData
sResponse = .responseText
End With

nStartPos = InStr(1, sResponse, "Total Est. Distance:", vbTextCompare)
If nStartPos Then
nStartPos = nStartPos + 36
nEndPos = InStr(nStartPos, sResponse, "miles", vbTextCompare) - 1
If nEndPos = nStartPos Then gnDrivingDistance = _
CInt(Mid$(sResponse, nStartPos, nEndPos - nStartPos + 1))
Else
gnDrivingDistance = 0
End If

Set xml = Nothing
End Function

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]


Steve E wrote:
I am trying to pass address information located in cells to mapquest
(or some similar site) to find driving distance between two
locations. Driving distance is then used to calculate other costs in
application. Cells are named: "To_Street", "To_City", "To_State",
"To_Zip" and "From_Street", "From_City", "From_State", "From_Zip"

The hyperlink url should end up something like
http://www.mapquest.com/directions/m...S&cid=lfddlink

This would allow my user to click on the url and get the mapquest
site to open with the addresses entered so that they have to click on
the "get directions" and then manual enter back in the driving
distance (I'd really like to automate that but I don't think I'm up
to that yet.

Is this possible from within Excel?