Public Sub calcul_duree_trajet()
Dim feuil_donnees_brutes As Excel.Worksheet
Dim feuil_google As Excel.Worksheet
Set feuil_donnees_brutes = Sheets("Données_Brutes")
Set feuil_google = Sheets("Feuil2")
Derniere_ligne = feuil_profil.Cells.Find(what:="*", SearchDirection:=xlPrevious).Row
For i = 10 To Derniere_ligne
Depart = feuil_donnees_brutes.Cells(i, 22)
Arrivee = feuil_donnees_brutes.Cells(i, 21)
If Not IsEmpty(Arrivee) Then
With feuil_google.QueryTables.Add(Connection:="URL;http://maps.google.fr/maps?f=d&saddr=" & Depart & "&daddr=" & Arrivee, Destination:=feuil_google.Range("A1"))
.Name = "itinéraire"
.BackgroundQuery = True
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.Refresh BackgroundQuery:=False
End With
Set Result = feuil_google.Cells.Find("Itinéraires possibles")
If Result Is Nothing Then
feuil_donnees_brutes.Cells(i, 10) = "Chantier introuvable"
Else
duree_un_trajet = Result.Offset(1, 0)
feuil_donnees_brutes.Cells(ligne_projet, 24) = duree_un_trajet
End If
End If
Next i
End Sub