Parse JSON into Excel

[scarlett3]

Hi

I have been using the Jsonconverter vba parser available via https://github.com/VBA-tools/VBA-JSON to extract the data from json files such as https://stats.europeantour.com/v1/season/2019/players/strokes-gained/off-the-tee. This has been very easy to set up and it works.

However, the following json url looks virtually identical - https://stats.europeantour.com/api/v2/events/2019084/stats/strokes-gained-off-the-tee - but running the SGOTT2 macro in the file below generates a 'Run-time error '13': Type mismatch' error.

The file is available here: https://www.dropbox.com/s/913vec5i8rmf8kw/book1.xls?dl=0

I can't see what the issue is to fix.

Can anyone help?

Thanks

 

[Norie]

ParseJson in SGOTT2 returns a dictionary with 2 keys, 'statsDetailedType' and 'statsData'.

The value for the first key is simply the string 'STROKES_GAINED_OFF_THE_TEE', whereas the value for 'statsData' is a dictionary.

To access that dictionary try this.

    For Each Item In JSON("statsData")

        Sheets("OTT").Cells(i, 7).Value = Item("firstName")
        Sheets("OTT").Cells(i, 8).Value = Item("lastName")
        Sheets("OTT").Cells(i, 9).Value = Item("average")
        Sheets("OTT").Cells(i, 10).Value = Item("totalRounds")
        Sheets("OTT").Cells(i, 11).Value = Item("id")

        i = i + 1
   Next Item

 

[scarlett3]

Thanks Norie! :D