Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Looping code to look up distance between addresses in google maps 1

Status
Not open for further replies.

jlroeder

Technical User
Sep 18, 2009
91
US
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", " _
& 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
 
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:
[blue]
Dim intR As integer
intR = 2[/blue]
...[blue]
Do While Range("D" & intR).Value <> ""[/blue]
  myRequest.Open "Get", "[URL unfurl="true"]http://maps.googleapis.com/maps/api/directions/xml...="[/URL] _
    & Range("D" [blue]& intR[/blue]).Value & "&destination=" & Range("E"[blue] & intR[/blue]).Value & "&sensor=false", False
[blue]
  intR = intR + 1
Loop[/blue]
...

Have fun.

---- Andy

There is a great need for a sarcasm font.
 
@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 [blue]Great Post![/blue] 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,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top