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

**gelwc** · 10/04/2015, 10h37

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

**PMO2017** · 10/04/2015, 18h44

Bonjour,

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

**PMO2017** · 11/04/2015, 08h03

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

"<?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.

**patricktoulon** · 11/04/2015, 09h30

bonjour pour info
Pièce jointe 174451

**PMO2017** · 11/04/2015, 10h19

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.

**eriiic** · 11/04/2015, 10h37

Bonjour,

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

**patricktoulon** · 11/04/2015, 12h37

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

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

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager