API REST JSON depuis Excel et VBA

Depuis plusieurs mois/années les développements d'applications sont découpées et offres de plus en plus d'APIs.
Le standard de facto est aujourd'hui l'API en REST / JSON, un monde sans état.

Macro Excel VBA-Web

Il existe une macro VBA disponible sur github qui permet facilement d'appeler une API Rest / Json.
Je l'ai utilisée par exemple pour extraire automatiquement l'ensemble des issues d'un repo GitLab mais nous allons l'utiliser ici avec un autre service.

Exemple pour les pressés

Pour note exemple, nous allons interroger des données de Pays disponible sur restcountries.eu.
Voici ci-dessous le code de démo. L'URL sera bien évidemment construite avec les bons paramètres.

Public Sub InterrogationPays()
    Dim Url As String
    Dim dataFromResponse As Collection
    
    Dim Client As New WebClient
    Dim Request As New WebRequest
    Dim Response As New WebResponse
    
    EnableLogging = True
    
    Client.BaseUrl = "https://restcountries.eu/rest/v2/name/fra"
    
    Set Response = Client.Execute(Request)
    
    If (Response.StatusCode = Ok) Then
        Set dataFromResponse = Response.Data
        ProcessResponse dataFromResponse
    End If
End Sub

Private Sub ProcessResponse(dataResponse As Collection)
    Dim ws As Worksheet
    Dim dataItem As Object
    Dim i As Integer
    Set ws = ActiveWorkbook.Worksheets(1)
    
    ws.Range("A1").Value = Now
    i = 0
    For Each dataItem In dataResponse
        ws.Range("B2").Offset(i).Value = dataItem.Item("name")
        ws.Range("C2").Offset(i).Value = dataItem.Item("capital")
        ws.Range("D2").Offset(i).Value = dataItem.Item("population")
        i = i + 1
    Next
End Sub

et nous obtenons:

Utilisation

Il suffit de télécharger les modules depuis le repo github.
Dézipper le contenu et vous pouvez utiliser directement la version VBA-Web - Blank.xlsm. Il s'agit d'un fichier vide (comme son nom l'indique) qui a les modules nécessaires déjà chargés.

Ouvrez le fichier Excel et allez dans l'éditeur Visual Basic et vous devez voir l'ensemble des classes disponibles.

La documentation est très fournie.

Son utilisation est classique:

• vous commencez par définir votre point d'entrée à l'API, c'est à dire l'URL que vous pouvez construire dynamiquement avec une base qui varie peu et un chemin qui peut evoluer avec différentes versions d'APIs et enfin des paramètres.
• vous pouvez définir d'autres paramètres à votre requete (comme les éléments du header)
• vous executez votre requete qui vous retourne (normalement) une instance de Response qui vous reste ensuite à processer.

L'outil vous met à disposition un grand nombre d'aides qui facilitent les types de requetes les plus courantes comme un Get ou PostJSON, les objets Headers...

Mise à jour - Février 2019

Suite aux commentaires, je vais essayer de clarifier la gestion de la réponse JSon.

L'API retourne un objet simple:

Dans ce premier exemple, quand on interroge notre API, elle retourne un objet simple, c'est à dire commençant par "{".

Dans ce cas, notre objet response doit être de type Dictionary, comme ci-dessous:

Sub sampleObj()
    Dim client As New WebClient
    Dim request As New WebRequest
    Dim response As New WebResponse
    
    Dim responseData As Dictionary
            
    EnableLogging = True
    
    client.BaseUrl = "http://localhost:8080/obj"
    Set response = client.Execute(request)
    
    If (response.StatusCode = Ok) Then
        Set responseData = response.Data
        Debug.Print "Name: " & responseData("Name")
    End If
End Sub

ce qui nous donne

et maintenant, si on utilise une Collection pour notre responseData, nous avons le problème d'imcompatibilité de type, erreur 13: il n'arrive pas à transformer votre objet unitaire en une liste d'objets. D'où l'incompatibilité de type.

L'API retourne un tableau d'objets:

Vous devez vous en douter si vous avez bien suivi, dans ce cas notre responseData doit être de type Collection.

et voilà l'exemple pour récuperer le premier élement de notre liste d'objets. A noter que cette fois, il faut prendre le Item X de votre liste puis le champ voulu.

Sub sampleObj()
    Dim client As New WebClient
    Dim request As New WebRequest
    Dim response As New WebResponse
    
    Dim responseData As Collection
            
    EnableLogging = True
    
    client.BaseUrl = "http://localhost:8080/array"
    Set response = client.Execute(request)
    
    If (response.StatusCode = Ok) Then
        Set responseData = response.Data
        Debug.Print "Name: " & responseData.Item(1)("Name")
    End If
End Sub

et on obtient

Si vous typé votre responseData en Dictionary, vous obtenez une erreur 13 d'incompataiblité de type.

Et pour les structures plus complexe, vous mixez ces 2 types.

Et pout être sûr,

vous mettez un point d'arrêt au niveau du retour du serveur (par exemple sur le test du status retourné) et vous regardez dans la fenêtre des espions le type de response.Data.