How to pull JSON values into Excel sheet

Question

I am trying to pull JSON values from a URL that I am working with at the moment. I may have done something like this before but I dont know what I'm missing here.

Here is the URL - https://eu-offering.kambicdn.org/offering/v2018/888/listView/golf.json?lang=en_GB&market=GB&client_id=2&channel_id=1&ncid=1568916879040&useCombined=true And an image for clarity of what is needed to be extracted.

I ran a test using Tinman's approach as can be found here - How to get, JSON values to Work in VBA-JSON? , but i can't even apply his function, PrintJSONAccessors(), here

Public Sub exceljson()
Dim http As Object
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", 
"https://eu-offering.kambicdn.org/offering/v2018/888/listView/golf.json?lang=en_GB&market=GB&client_id=2&channel_id=1&ncid=1568916879040&useCombined=true", False
http.Send

Dim results As Variant
results = BitfinexTextToArray(http.responseText)

Worksheets(1).Range("A1").Resize(UBound(results), UBound(results,2)).Value = results

MsgBox ("complete")
End Sub

Function BitfinexTextToArray(responseText As String) As Variant
Dim item As Variant, JSON As Object
Dim MaxColumns As Long

Set JSON = ParseJson(responseText)

For Each item In JSON
    If item.Count > MaxColumns Then MaxColumns = item.Count
Next

Dim results As Variant
ReDim results(1 To JSON.Count, 1 To MaxColumns)

Dim c As Long, r As Long
For Each item In JSON
    r = r + 1

    For c = 1 To item.Count
        results(r, c) = item(c)
    Next
Next

BitfinexTextToArray = results
End Function

I need help with pulling the following item values from each of the JSON "event" 1. "englishName" 2. "participant" 3. "oddsFractional"

Seems the actual JSON content would be more useful than the request URL — Mathieu Guindon, Sep 19 '19 at 21:09
It sure does, but do note that many people don't download images; as with code, it's probably a better idea to include it as actual text content. Cheers! (and good luck!) — Mathieu Guindon, Sep 19 '19 at 21:33
Pls paste this URL - https://eu-offering.kambicdn.org/offering/v2018/888/listView/golf.json?lang=en_GB&market=GB&client_id=2&channel_id=1&ncid=1568916879040&useCombined=true in this json formatter online - https://jsonformatter.curiousconcept.com/ and click the PROCESS button. You'll get the actual text content — Smith O., Sep 19 '19 at 21:37

score 0 · Answer 1 · answered Sep 19 '19 at 21:52

NOTE: my example uses the JsonConverter library and requires you to add a reference to the Microsoft Scripting Runtime to access the Dictionary object.

I set up a test file with JSON loaded from your URL above. After parsing the JSON data, the exercise becomes understanding how the various levels are nested and what type of data structure is being used. In your JSON, it's a mix of Collection, Array, and Dictionary in various combinations. My example below shows how you have to stack up these nested references to get the data you're looking for.

Review the information in this answer to understand how the JSON is parsed into a hierarchical data structure.

Option Explicit

Public Sub test()
    Dim fileNum As Long
    fileNum = FreeFile()

    Dim filename As String
    filename = "C:\Temp\testdata.json"

    Dim jsonInput As String
    Open filename For Input As #fileNum
    jsonInput = Input$(LOF(fileNum), fileNum)
    Close fileNum

    Dim json As Object
    Set json = ParseJson(jsonInput)

    Debug.Print "   English Name = " & json("events")(1)("event")("englishName")
    Debug.Print "    Participant = " & json("events")(1)("betOffers")(1)("outcomes")(2)("participant")
    Debug.Print "Odds Fractional = " & json("events")(1)("betOffers")(1)("outcomes")(2)("oddsFractional")
End Sub

An even better solution will be to create an intermediate variable and then loop over the contents in an array (or collection or dictionary).

How to pull JSON values into Excel sheet

1 Answers1