How can I pass values in cells to hyperlink to get result

[Steve E]

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/...City&2s=To_State&2z=To_Zip&2y=US&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?

 

[Steve E]

Sorry.

I figured this out using the worksheet function HYPERLINK(). Works great.

Now if I can just figure out how to capture the weblink data back...

 

[Jake Marx]

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]

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

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]

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/...City&2s=To_State&2z=To_Zip&2y=US&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?

 

[Jake Marx]

Hi Steve,

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...

Glad you liked it. I would not use something like this in a production
environment for a few reasons: 1) it's wholly dependent on the mapquest
website - any changes to the form or results page, and it likely won't work,
2) there's no error handling, and 3) there's no way to communicate to the
user that mapquest needs some clarification - it just fails if the user's
address doesn't match exactly what mapquest is expecting. But it's a fun
excercise. <g>

If you want something more robust, most mapping systems provide some type of
API that you can hit with your code. Here's a link to mapquest's:

http://www.mapquest.com/features/main.adp?page=developer_tools_oapi

Thanks a million!

You're very welcome!

--
Regards,

Jake Marx
www.longhead.com


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