Home / Questions / 336377
Accepted
Huntzberger
Asked: 2020-03-12 11:03:19 +0800 CST 2020-03-12 11:03:19 +0800 CST

Consume an API from VBA excel

  • 772

Hello, it happens that I am consuming an API from which they give me the URL to which I must add some parameters so that it returns a result.

This API returns a JSON which I must load into a table in Excel

I based on this page

This is my code that finally works but the strange thing is that it does not bring me the position and orientation data

Option Explicit

Sub TestTablas()
    Dim Tablas As QueryTable
    Dim url As String
    Dim id As Integer
    Dim startDate As String
    Dim endDate As String

    Dim req As Object
    Dim strjson As String
    Dim jp As Object
    Dim dict
    Dim subdict
    Dim subsubdict
    Dim i As Integer
    Dim roleJson As String
    Dim jpRoles As Object
    Dim items
    Dim itemsId
    Dim attr
    Dim itemattr
    
    id = Hoja10.Range("C2")
    startDate = Format(Hoja10.Range("C3"), "yyyy-mm-dd hh:mm:ss")
    endDate = Format(Hoja10.Range("C4"), "yyyy-mm-dd hh:mm:ss")
    url = "https://xxx.yyy.com/api/zzz?id=" & id& "&startDate=" & startDate & "&endDate=" & endDate & "&timezone=-6"
        
    Set req = CreateObject("MSXML2.XMLHTTP")
    req.Open "GET", url, False
    req.Send
    
    Set jp = JsonConverter.ParseJson(req.ResponseText)
    Set items = jp("items")
    
    roleJson = req.ResponseText
    Set jpRoles = JsonConverter.ParseJson(roleJson)
    Set items = jpRoles("items")
    
    i = 7
    For Each itemsId In items
        For Each attr In itemsId
           ' Debug.Print attr
            'Debug.Print itemsId(attr)
            
            Sheets("Informacion").Cells(i, 1).Value = itemsId("name")
            Sheets("Informacion").Cells(i, 2).Value = itemsId("Position")
            Sheets("Informacion").Cells(i, 3).Value = itemsId("positionDate")
            Sheets("Informacion").Cells(i, 4).Value = itemsId("latitude")
            Sheets("Informacion").Cells(i, 5).Value = itemsId("longitude")
            Sheets("Informacion").Cells(i, 6).Value = itemsId("Orientation")
            Sheets("Informacion").Cells(i, 7).Value = itemsId("speed")
            Sheets("Informacion").Cells(i, 8).Value = itemsId("receiptDate")
            Sheets("Informacion").Cells(i, 9).Value = itemsId("notification")
            Sheets("Informacion").Cells(i, 10).Value = itemsId("kilometers")
            Sheets("Informacion").Cells(i, 11).Value = itemsId("unitVoltage")
            Sheets("Informacion").Cells(i, 12).Value = itemsId("gpsVoltage")
            Sheets("Informacion").Cells(i, 13).Value = itemsId("satellites")
            
        Next attr
         i = i + 1
    Next itemsId
    
End Sub

enter image description here

JSON example

enter image description here

json

1 Answers

  1. Best Answer

    Properties of JSON objects are usually case sensitive , which means that Positionand positionor Orientationand orientationare not the same thing.

    In the image it is clear that those properties in the JSON are in lowercase, but you are looking for them as uppercase properties.

    If you put it like this, it should work:

            Sheets("Informacion").Cells(i, 2).Value = itemsId("position")
        ...
        Sheets("Informacion").Cells(i, 6).Value = itemsId("orientation")
    • 1