Parsing JSON in Excel VBA and access array by key

Question

I'm trying to scrape the content from a website (Link) using Excel VBA. Here is the .json response from the server (Link):

{
  "TopicDetails": {
    "type": 0,
    "ccm2Id": 31088568,
    "cftId": 0,
    "identifier": "FETOPEN-01-2018-2019-2020",
    "title": "FET-Open Challenging Current Thinking",
    "actions": [
      {
        "status": {
          "id": 31094502,
          "abbreviation": "Open",
          "description": "Open"
        },
        "types": [
          "RIA Research and Innovation action"
        ],
        "plannedOpeningDate": "07 November 2017",
        "submissionProcedure": {
          "id": 31094504,
          "abbreviation": "multiple cut-off",
          "description": "multiple cut-off"
        },
        "deadlineDates": [
          "16 May 2018",
          "24 January 2019",
          "18 September 2019",
          "03 June 2020"
        ]
      }
    ]
  }
}

For this reason, a macro was written, which works quite good. However, I'm experiencing difficulties to access information stored in the block "actions" (especially the data with the key "types" and the latest deadline date). The error message is "Subscript out of range".

Here is the relevant part of my code:

Private Sub getJson()

Dim http As Object
Dim JSON As Object
Dim response As String
Dim url As String
Dim id As String
Dim oTarget As Object

id = "FETOPEN-01-2018-2019-2020"
url = "https://ec.europa.eu/info/funding-tenders/opportunities/data/topicDetails/" & LCase(id) & ".json?lang=en"

Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", url, False
http.send
response = http.responseText

Set JSON = JsonConverter.ParseJson(response)

'--- WORKS ---
Cells(5, 11).Value = JSON("TopicDetails")("title")

'--- DOESN'T WORK ---
'--- Test 1 ---
Cells(5, 17).Value = JSON("TopicDetails")("actions")("types")
'--- Test 2 ---
Cells(5, 18).Value = JSON("TopicDetails")("actions")(0)
'--- Test 3 ---
Cells(5, 19).Value = JSON("TopicDetails")("actions")(0)("types")
'--- Test 4 ---
Set oTarget = JSON("TopicDetails")("actions")
With oTarget
    Cells(5, 18).Value = .item(0).item(0)
End With

End Sub

While trying to approach the elements of the "actions" array, I found out, that following code delivers 1 as an answer (which makes sense):

Set oTarget = JSON("TopicDetails")("actions")
Cells(5, 18).Value = oTarget.count

At the same time, while trying to approach the next level of the array, the following code delivers an error ("Subscript out of range"), instead of a 5, as someone could expect:

Set oTarget = JSON("TopicDetails")("actions")(0)
Cells(5, 18).Value = oTarget.count

How can I extract the information "RIA Research and Innovation action" (has the key "types") and the latest deadline date 03 June 2020 (has the key "deadlineDates")?

Thank you in advance! Any help is much appreciated!

Maksim

Answer 1

it's because the datatype "types" is array.

According the VBA-JSON Examples https://github.com/VBA-tools/VBA-JSON the array index starts at 1.

Try this:

Cells(5, 19).Value = JSON("TopicDetails")("actions")(1)("types")(1)