Parse JSON into Excel

scarlett3

Board Regular
Joined
Jan 21, 2005
Messages
62
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,275
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.
Code:
    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
 

Forum statistics

Threads
1,077,822
Messages
5,336,564
Members
399,088
Latest member
Swindlestikz

Some videos you may like

This Week's Hot Topics

Top