Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I pass values in cells to hyperlink to get result
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I pass values in cells to hyperlink to get result
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... "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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I pass values in cells to hyperlink to get result
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I pass values in cells to hyperlink to get result
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I pass values in cells to hyperlink to get result
Hi Steve,
Steve E wrote: 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/mai...per_tools_oapi Thanks a million! You're very welcome! -- Regards, Jake Marx www.longhead.com [please keep replies in the newsgroup - email address unmonitored] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste Hyperlink result as working hyperlink | Excel Worksheet Functions | |||
using values entered in cells to determine a result in another cel | Excel Discussion (Misc queries) | |||
Call VBScript from VBA, pass variable, return result to macro? | Excel Programming | |||
How can I pass an array as TextToDisplay to a hyperlink? | Excel Programming | |||
can variables pass values | Excel Programming |