INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Looping code to look up distance between addresses in google maps

Looping code to look up distance between addresses in google maps

(OP)
Sub GoogleMaps()

Dim myRequest As XMLHTTP60
Dim myDomDoc As DOMDocument60
Dim journey As IXMLDOMNode

I've got this code to pull a distance between addresses from google maps. How can I modify it to run in a loop starting in row 2 and stop when it sees no more address's. The origin address is in column D, the destination is in column E, and the mileage is in column F.


Set myRequest = New XMLHTTP60
myRequest.Open "Get", "http://maps.googleapis.com/maps/api/directions/xml...=" _
& Range("d13").Value & "&destination=" & Range("E13").Value & "&sensor=false", False
myRequest.send
Set myDomDoc = New DOMDocument60
myDomDoc.LoadXML myRequest.responseText
Set journey = myDomDoc.SelectSingleNode("//leg/distance/value")

Range("F13").Value = Round(journey.Text / 1000 / 1.609344, 0)

exitRoute:

Set journey = Nothing
Set myDomDoc = Nothing
Set myRequest = Nothing

End Sub

RE: Looping code to look up distance between addresses in google maps

I assume you want to loop this line of code thru all rows - starting in row 2 - until there is no more data in column D

CODE


Dim intR As integer
intR = 2
...
Do While Range("D" & intR).Value <> ""
  myRequest.Open "Get", "http://maps.googleapis.com/maps/api/directions/xml...=" _
    & Range("D" & intR).Value & "&destination=" & Range("E" & intR).Value & "&sensor=false", False

  intR = intR + 1
Loop
... 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Looping code to look up distance between addresses in google maps

(OP)
Yeah that was the idea.

RE: Looping code to look up distance between addresses in google maps

(OP)
Thanks, It works like a champ.

RE: Looping code to look up distance between addresses in google maps

@jlroeder, you've been posting here at Tek-Tips for seven years now, got some good tips, it seems, yet you have never once hit the Great Post! link to award a Little Purple Star. These tokens not only demonstrate your appreciation but also identify worth while tips for other members browsing the forum. How about joining the action.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close