Géocoder adresses puis les récupérer dans feuille excel

Version imprimable

Bonjour à tous,

Je travail sur un script qui va géocoder des adresses contenues dans une feuille excel puis remplir cette même feuille avec les latitudes et longitudes obtenues. Pour géocoder je créé un page web dynamiquement qui va interroger l'api google.

Je n'ai qu'un problème c'est comment récupérer les latitudes et longitudes pour les intégrer dans excel. Si quelqu'un avait un exemple même très basique je suis preneur.

Par exemple créer une page web dynamiquement qui va faire cellule 1.1 + cellule 1.2 (une fonction en javascript) et qui va renvoyer le résultat dans la cellule 1.3.

Le début du script pourrait ressembler à ça :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Sub RécupLatln() Dim a as string Dim b as string a = cells(1,1). value b = cells(1,2). value Open "page test" For Output As #1 Print #1, Chr$(13) & "<html>" Print #1, Chr$(13) & " <head>" Print #1, Chr$(13) & " <script type=" & Chr(34) & "text/javascript" & Chr(34) & ">" Print #1, Chr$(13) & " var ch1= " & a & ";" Print #1, Chr$(13) & " var ch2= " & b & ";" Print #1, Chr$(13) & " function (){" Print #1, Chr$(13) & " var ch1= " & a & ";" Print #1, Chr$(13) & " var ch2= " & b & ";" Print #1, Chr$(13) & " Add=ch1+ch2; " Print #1, Chr$(13) & " }" Print #1, Chr$(13) & " </script>" Print #1, Chr$(13) & " </head>" Print #1, Chr$(13) & " <body >" Print #1, Chr$(13) & " </body>" Print #1, Chr$(13) & " </html>" Close #1 End Sub
Ensuite il faudrait trouver un moyen de récupérer la valeur Add.

L'objectif final étant de faire tourner un script un WE complet pour récupérer des coordonnées d'un grand nombre d'adresse (l'api Google limite le géocodage).

Merci d'avance pour la réponse.

1 pièce(s) jointe(s)

Bonjour,

Une piste avec une fonction personnalisée qui utilise msxml2.dll et googleapis maps

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
'#######################################
'### Pour le Early Binding
'### nécessite la référence
'### Library MSXML2
'### C:\WINDOWS\system32\msxml2.dll
'### Microsoft XML, v2.6
'#######################################
Function PointGeodesique(Plage_AdresseCodeVillePays As Range) As String
Dim XmlH As Object    '(Late Binding) MSXML2.XMLHTTP
Dim C As Range
Dim Url$
Dim A$
Dim Latitude$
Dim Longitude$
'--- Chaîne des paramètres ---
For Each C In Plage_AdresseCodeVillePays
  If Trim(C) <> "" Then
    Url$ = Replace(Trim(C), Space(1), "+") & ",+"
  End If
Next C
Url$ = Url$ & "&sensor=false"
'--- GoogleAPIs ---
Url$ = "http://maps.googleapis.com/maps/api/geocode/xml?address=" & Url$
'--- MSXML2.XMLHTTP ---
Set XmlH = CreateObject("msxml2.xmlhttp")
With XmlH
  .Open "get", Url$, False
  .Send
  A$ = Trim(.responseText)
End With
'--- Latitude et longitude ---
Latitude$ = Right(A$, Len(A$) - InStr(1, A$, "<lat>") - 4)
Latitude$ = Left(Latitude$, InStr(1, Latitude$, "</lat>") - 1)
Longitude$ = Right(A$, Len(A$) - InStr(1, A$, "<lng>") - 4)
Longitude$ = Left(Longitude$, InStr(1, Longitude$, "</lng>") - 1)
'--- Retour ---
PointGeodesique = "Latitude:" & Latitude$ & " Longitude:" & Longitude$
End Function

Exemple d'utilisation :
On met des données en A1:D1 (rue, code postal, ville, pays) et on tape la formule =PointGeodesique(A1:D1) dans une autre cellule.

11/04/2015, 08h03
PMO2017
Citation:

L'objectif final étant de faire tourner un script un WE complet pour récupérer des coordonnées d'un grand nombre d'adresse (l'api Google limite le géocodage).

Le code que j'ai précédemment fourni ne fonctionnera pas pour les 2 raisons suivantes

1) Erreur de logique
Une instruction est erronée
Code:

1 2 3 4 5 For Each C In Plage_AdresseCodeVillePays If Trim(C) <> "" Then Url$ = Replace(Trim(C), Space(1), "+") & ",+" End If Next C
il faut écrire
Code:

1 2 3 4 5 For Each C In Plage_AdresseCodeVillePays If Trim(C) <> "" Then Url$ = Url$ & Replace(Trim(C), Space(1), "+") & ",+" End If Next C
2) Quota de requêtes dépassé (rédhibitoire)
Après plusieurs usages, j'obtiens

Citation:

"<?xml version=""1.0"" encoding=""UTF-8""?>
<GeocodeResponse>
<status>OVER_QUERY_LIMIT</status>
<error_message>You have exceeded your daily request quota for this API.</error_message>
</GeocodeResponse>"

Il faut se tourner vers une autre piste.
Si j'en trouve une, je la communiquerai.
A suivre.
11/04/2015, 09h30
patricktoulon

heu

bonjour pour info
Pièce jointe 174451
11/04/2015, 10h19
PMO2017

Merci Patrick pour ces infos.
***
J'en déduis qu'il ne faut pas passer par une fonction personnalisée car, à chaque changement sur la feuille, la fonction se recalcule.
De ce fait, on a vite atteint la limite autorisée.
11/04/2015, 10h37
eriiic

Bonjour,

Citation:

J'en déduis qu'il ne faut pas passer par une fonction personnalisée car, à chaque changement sur la feuille, la fonction se recalcule

.
Normalement, si elle n'est pas volatile, une fonction ne se recalcule que si un antécédent est modifié.

eric
11/04/2015, 12h37
patricktoulon

Citation:

Envoyé par PMO2017

Merci Patrick pour ces infos.
***
J'en déduis qu'il ne faut pas passer par une fonction personnalisée car, à chaque changement sur la feuille, la fonction se recalcule.
De ce fait, on a vite atteint la limite autorisée.

re

tu n'a qua passer par par une astuce toute simple
en début de macro
tu écrit la formule dans les cellules dynamiquement
et des que la valeur est autre que rien du copy paste value tu garde donc la valeur des point geo mais plus la formule
sur tout que la c'est simple c'est la même formule pour toute
ou alors dans une boucle temporisé intelligemment tu applique la formule

ou même mieux
ta formule truc = ta fonctio blablabla
et bien toute les cellule après la première tu remplace ta fonction par une condition et ta fonction
ce qui fait que tant que les précédente ne sont pas fait la fonction n'est pas sollicitée
ca ralentirait considérablement

et la on serait avec l'attente de la requette etc.... largement dans les 5 par secondes

c'est tout bête

re
ou alors même mieux

utiliser 2 colonne
la première colonne vide
la 2 eme avec un condition sur la cellule de gauche si la gauche est vide >ta fonction sinon rien et en retour de la fonction du pourrait lui donner la va valeur "fait"

de ce fait même avec le calculate la fonction n'est pas sollicitée si la cellule gauche n'est pas vide
11/04/2015, 14h53
eriiic

Re,

Citation:

utiliser 2 colonne
la première colonne vide
la 2 eme avec un condition sur la cellule de gauche si la gauche est vide >ta fonction sinon rien et en retour de la fonction du pourrait lui donner la va valeur "fait"

Est-ce bien nécessaire vu qu'une fonction non volatile est recalculée uniquement lorsque les variables d’entrée changent ?
Il y a des cas particuliers ?

eric