XL: ping mapquest or googlemaps to get driving time + distance?

  • Thread starter Thread starter ker_01
  • Start date Start date
K

ker_01

Has anyone used Excel to post addresses to any mapping website and scrape the
resulting driving distance (milage) and travel time? I'm not worried about
the actual directions, just getting these two numbers. I have a spreadsheet
with several thousand 'To:' and 'From:' addresses, and want to be able to
cycle through them without doing it via cut/paste.

I appreciate any existing code snippets you'd be willing to share!

Thank you,
Keith
 
Excellent, thank you- very easily adapted to give both time and distance.
Best,
Keith
 
Hi, JP. I can't seem to get this to work; have set all my references.
However, it will pull up Mapquest, insert the addresses and bring up the
map/info, but it stops there and doesn't return the result to the cell or
continue on. I don't get any error messages; it just stops. Do you have any
idea of what I'm doing wrong; I didn't change any code and have my start/end
addresses all in Row 1, starting with Col A?
 
Paige-

I had to change the search string in two places in the code (the text string
it searches for on the Mapquest page has changed to "Total Travel
Estimates:"). I expanded the length of the returned string and usedtext
string commands (Instr, Left, Right, Trim, etc) to grab the parts.

With regex
.Pattern = "Total Travel Estimates:"
.MultiLine = False
End With

and

If Regmatch.Count > 0 Then
GetFirstPos = WorksheetFunction.Find("Total Travel Estimates:", BodyTxt,
1)
GetDistance1 = Mid(BodyTxt, GetFirstPos + 23, 100)
GetDistance2 = Trim(Left(GetDistance1, InStr(GetDistance1, "Fuel Cost:")
- 1))
GetMiles = Trim(Right(GetDistance2, Len(GetDistance2) -
InStr(GetDistance2, "/")))
GetMiles2 = Val(Trim(Left(GetMiles, InStr(GetMiles, "miles") - 1)))
GetTT = Trim(Left(GetDistance2, InStr(GetDistance2, "/") - 1))
DoEvents
GetDistance = GetMiles2
Else
GetDistance = "Address Error, fix and try again"
End If

So far, I'm just pulling milage and not the travel time, but it's there if
you need it.

If you still have problems, probably best to post your entire sub with all
edits, and indicate which line the code is stopping on.

Best,
Keith
 
Back
Top