Bonjour,
je t'ai crée une macro newdistance qui utilise le code que j'avais proposé.
ce code suppose l'introduction d'une adresse de départ et de destination valables en colonne K et L. et d'activer la référence à la librairie microsoft xml 3.0.
Sub newdistance()
Dim d As Long
Dim t As Long
Dim i
i = 2
While Cells(i, 1) <> ""
frain = Cells(i, "K")
If frain <> "" Then
'frain = InputBox("point de départ (gps (lat, long) ou adresse")
fra = Replace(frain, " ", "+")
'toain = InputBox("point d'arrivée (gps (lat, long) ou adresse")
toain = Cells(i, "L")
toa = Replace(toain, " ", "+")
Call GoogleGetRoute(fra, toa, d, t)
'MsgBox "de " & frain & " à " & toain & vbCrLf & "distance : " & Format(d / 1000, "0.0") & " km, durée : " & Format(t / 86400, "hh:mm")
Cells(i, "E") = d / 1000
Cells(i, "E").NumberFormat = "0.0"
Cells(i, "f") = t / 86400
Cells(i, "F").NumberFormat = "hh:mm"
i = i + 1
End If
Wend
End Sub
Public Sub GoogleGetRoute(ByVal Fromaddr As String, ByVal Toaddr As String, ByRef rdistance As Long, ByRef rtime As Long, Optional ByVal traceon As Boolean = False)
' Eric De Schrevel 2013, merci de laisser cette référence si vous utilisez ce code
' <!-- e --><a href="mailto:eric.de.schrevel@gmail.com">eric.de.schrevel@gmail.com</a><!-- e -->
' rajouter la référence à microsoft xml 3.0, via le menu developer Tools pour que ceci fonctionne
Dim request As MSXML2.XMLHTTP
Dim xmlresponse As MSXML2.DOMDocument
Dim leg As IXMLDOMNode
Dim googleurl As String
Dim ctr As Integer
rdistance = -1: 'pas parvenu à calculer la distance
rtime = 0
googleurl = "http://maps.google.com/maps/api/directions/xml?sensor=false&mode=DRIVING&origin=" & Fromaddr & "&destination=" & Toaddr
Set request = New MSXML2.XMLHTTP
responsestatus = ""
While responsestatus <> "OK"
request.Open "GET", googleurl, False
request.send
If traceon Then MsgBox request.responseText
Set xmlresponse = request.responseXML
responsestatus = xmlresponse.SelectSingleNode("DirectionsResponse/status").Text
If responsestatus = "OK" Then
ctr = 0
Set leg = xmlresponse.SelectSingleNode("DirectionsResponse/route/leg")
If Not (leg Is Nothing) Then
rtime = CLng(leg.SelectSingleNode("duration/value").Text) ' en secondes
rdistance = CLng(leg.SelectSingleNode("distance/value").Text) 'en mètres
End If
Else
'limite à 2500 consultations de route par jour
'cfr developers.google.com/maps/documentation/directions
ctr = ctr + 1
If responsestatus = "OVER_QUERY_LIMIT" Then
Application.Wait Time + TimeSerial(0, 0, 1)
Else
ctr = 3
End If
If ctr = 3 Then MsgBox ("erreur " & responsestatus): responsestatus = "OK"
End If
Wend
Set leg = Nothing
Set xmlresponse = Nothing
Set request = Nothing
End Sub