XL 2021 Requête API - VBA Excel - Parse JSON

[picc]

Bonjour à tous,

Nouveau sur le forum, je poste ici mon premier fil de discussion car j'ai besoin d'aide sur mon projet, j'ai cherché longtemps sans trouver de réponse. Je précise que je n'ai que quelques notions de base sur VBA, donc je vous remercie par avance pour votre aide, et je suis bien évidemment preneur de toutes vos suggestions sur ce projet.

Je travaille sur une cartographie interactive complexe à l'échelle de la région Auvergne Rhône-Alpes - tout en VBA - avec une méthode que j'ai trouvé sur internet, qui consiste à dessiner des formes - "Shapes" Excel, à partir de coordonnées géographiques (geoshapes). L'idée étant de pouvoir tracer différentes strates administratives (communes, EPCI, départements) à partir d'un jeu de données, et, ensuite, de pouvoir :
- interagir avec les objets shapes interactifs - en survol ou en cliquant dessus
- afficher différentes informations à travers les paramètres de format des shapes: des échelles de couleur appliquées aux Shapes, format des bordures etc.
- afficher un grand nombre de données sur la carte, selon mes besoins (nombre d'habitants, niveaux de revenus, taux de pauvreté, politique de la ville etc. Pour cela, je récupère mes données sur différents sites (data.gouv, INSEE, INED, SIG Villes etc. )
- placer des villes ou des points de repères toujours à partir de coordonnées géographiques
- mettre en place différentes fonctionnalités cartographiques, comme une barre de recherche d'adresse/localisation, des paramètres de Zoom, la possibilité de redimensionner la carte pour afficher les données à côté etc..
- pouvoir mettre à jour toutes les données, notamment les contours géographiques qui évoluent en 2025 avec la fusion de certaines communes, ou bien encore les données socio économiques, démographiques etc qui sont actualisées chaque année sur les sites de référence..

La méthode fonctionne, j'ai déjà pu la tester.

Mon premier gros travail consiste à récupérer toutes mes bases de données, les ordonner dans un fichier distinct et pouvoir les mettre à jour facilement. Pour cela j'ai opté pour les requêtes via Excel en VBA qui me permettent d'interroger différentes API disponibles. Je sais qu'il y a également la possibilité d'utiliser Power Query pour interroger des API mais pour mon usage je privilégie le VBA.

Pour ce qui est des contours administratifs, il y a beaucoup de sources sur internet (open street map..). J'ai d'abord récupéré mes coordonnées à partir de fichiers téléchargés sur Open Data Soft ou Data.gouv. (fichiers excel). Cependant, la mise à jour est fastidieuse, et m'obligerait à retélécharger les fichiers à chaque nouvelle mise à jour et à incrménter mes fichiers.

J'ai donc opté pour une mise à jour automatique et me suis intéressé aux requêtes API via Excel et au JSON.
A priori, la source officielle la plus fiable et mise à jour est la base de donnée ADMIN EXPRESS COG, deux possibilités pour la récupérer :
- récupérer et télécharger la dernière version du fichier ici https://www.data.gouv.fr/datasets/a...express-cog-carto-admin-express-cog-carto-pe/: il s'agit d'un fichier JSON ou GEOJSON qu'il faudra ensuite requêter en VBA. La encore, pour mettre à jour, cela implique de retélécharger à chaque fois la dernière version du fichier mise à jour et actualiser les données
- faire un appel / une requête API en ligne via du code VBA, ce qui permettra de mettre à jour les données facilement.

J'ai donc réussi à écrire en VBA un code qui permette d'interroger l'API geo.api.gouv et récupérer les données qui m'interessent. Pour retraiter et "parser" le JSON obtenu, relativement complexe, j'ai utilisé le module "JSON CONVERTER", un outil open source très bien fait et fonctionnel.

Malheureusement, je n'arrive pas à extraire précisemment les contours géographiques et je pense que cela est du au format du JSON et des coordonnées de type : [x.xxxx,y.yyyyyy],[[x.xxxx,y.yyyyyy],[x.xxxx,y.yyyyyy], etc...

Voici la procédure d'appel, permettant de récupérer le contour de toutes les communes de l'Ain (01) par exemple (pas trouvé comment récupérer toutes les communes de la région en une seule requête) : https://geo.api.gouv.fr/departements/01/communes?fields=nom,code,contour

Avec mon code VBA, j'arrive à récupérer sans problème les champs "nom", "code", ou encore "contour" "type" et les afficher dans un tableau excel, mais pas le champ "contour" "geoshape" qui contient les coordonnées. J'aimerais pouvoir les récupérer dans la colonne C de mon fichier. A défaut, j'arrive néanmoins à récupérer les coordonnées par une autre méthode, à travers l'utilisation de la formule "SERVICEWEB" mais je n'arrive pas à la faire à partir de mon code VBA.

Voici le format du JSON obtenu :

[
{
"nom": "L'Abergement-Clémenciat",
"code": "01001",
"contour": {
"type": "Polygon",
"coordinates": [
[
[
4.958412,
46.153273
],
[
4.958121,
46.153159
],
[
4.957712,
46.152881
],
[
4.95757,
46.152746
],
[
4.957139,
46.152182
],
[
4.956948,
46.152024
],
[
4.95672,
46.151739
],
[
4.956433,
46.151085
],

Voici le code VBA qui permet de faire la requête API et de récupérer les données :

Sub ExtrairedonneesAPI()
Dim reponse As String
Dim JSON As Object
Dim httpRequest As Object

Set httpRequest = CreateObject("MSXML2.XMLHTTP")

httpRequest.Open "GET", "https://geo.api.gouv.fr/departements/01/communes?fields=nom,code,contour", False

httpRequest.Send

reponse = httpRequest.ResponseText

Set JSON = JsonConverter.ParseJson(reponse)

'JsonConverter.JsonOptions.UseDoubleForLargeNumbers = True

i = 2
For Each Item In JSON
Sheets(1).Cells(i, 1).Value = Item("nom")
Sheets(1).Cells(i, 2).Value = Item("code")
Sheets(1).Cells(i, 3).Value = Item("contour")("coordinates")


i = i + 1
Next

MsgBox ("complete")

End Sub

Merci pour votre aide !

 

[nullosse]

Salut,
tes coordonnées de contour sont dans une collection de collection après le passage par ParseJson.
Je ne sais pas comment tu veux les afficher dans ta feuille mais voici un code qui les affiche sous forme (a,b),(c,d) etc ....

Sub ExtrairedonneesAPI()
Dim reponse As String, JSON As Object, httpRequest As Object
Dim coords, contour, leContour As String
Set httpRequest = CreateObject("MSXML2.XMLHTTP")
httpRequest.Open "GET", "https://geo.api.gouv.fr/departements/01/communes?fields=nom,code,contour", False
httpRequest.Send
reponse = httpRequest.ResponseText
Set JSON = ParseJson(reponse)
'JsonConverter.JsonOptions.UseDoubleForLargeNumbers = True
i = 2
For Each Item In JSON
Sheets(1).Cells(i, 1).Value = Item("nom")
Sheets(1).Cells(i, 2).Value = Item("code")
leContour = ""
For Each contour In Item("contour")("coordinates")
    For Each coords In contour
        leContour = leContour + "(" + CStr(coords(1)) + ", " + CStr(coords(2)) + "),"
    Next
Next
Sheets(1).Cells(i, 3).Value = leContour
i = i + 1
Next
MsgBox ("complete")
End Sub

Nullosse

 

[picc]

Salut,
tes coordonnées de contour sont dans une collection de collection après le passage par ParseJson.
Je ne sais pas comment tu veux les afficher dans ta feuille mais voici un code qui les affiche sous forme (a,b),(c,d) etc ....

Sub ExtrairedonneesAPI()
Dim reponse As String, JSON As Object, httpRequest As Object
Dim coords, contour, leContour As String
Set httpRequest = CreateObject("MSXML2.XMLHTTP")
httpRequest.Open "GET", "https://geo.api.gouv.fr/departements/01/communes?fields=nom,code,contour", False
httpRequest.Send
reponse = httpRequest.ResponseText
Set JSON = ParseJson(reponse)
'JsonConverter.JsonOptions.UseDoubleForLargeNumbers = True
i = 2
For Each Item In JSON
Sheets(1).Cells(i, 1).Value = Item("nom")
Sheets(1).Cells(i, 2).Value = Item("code")
leContour = ""
For Each contour In Item("contour")("coordinates")
    For Each coords In contour
        leContour = leContour + "(" + CStr(coords(1)) + ", " + CStr(coords(2)) + "),"
    Next
Next
Sheets(1).Cells(i, 3).Value = leContour
i = i + 1
Next
MsgBox ("complete")
End Sub

Regarde la pièce jointe 1220420

Nullosse

Un grand Merci Nullosse pour ta réponse ! c'est exactement la solution à mon problème ça fonctionne parfaitement 🙂 Ca me permet de mieux comprendre aussi la manipulation de JSON avec une structure un peu complexe. J'aurais certainement beaucoup d'autres questions à vous soumettre sur mon projet, mais je vais essayer d'avancer par moi-même et de chercher les solutions avant de vous resolliciter, belle journée à vous,