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

[jlroeder]

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?origin="

_
& 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

 

[Andrzejek]

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

[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]

Yeah that was the idea.

 

[jlroeder]

Thanks, It works like a champ.

 

[SkipVought]

@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]
Just traded in my OLD subtlety...
for a NUance![/sub]