Error 10001 - When JSON Convert

jamescooper

Well-known Member
Joined
Sep 8, 2014
Messages
780
I am receiving this error message when pulling data from the web using JSON Convert:

Err.Raise 10001, "JSONConverter", json_ParseErrorMessage(json_String, json_Index, "Expecting 'STRING', 'NUMBER', null, true, false, '{', or '['")



This is the VBA I have:

Code:
Sub Dog_Form()


Dim LastRow     As Long
Dim x           As Long
Dim urls        As Variant
Dim dogLinks    As Variant
    
Application.ScreenUpdating = False
    
    LastRow = Sheets("Races").Range("I" & Rows.Count).End(xlUp).row
    urls = Sheets("Races").Range("I17:I19" & LastRow).Value
    
    Sheets("Dog Form").Visible = True
    
    For x = LBound(urls) To UBound(urls)
        dogLinks = getDogForm(urls(x, 1))
        Sheets("Dog Form").Cells(Sheets("Dog Form").Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(dogLinks), 20).Value2 = dogLinks
        
    DoEvents
    
    Next x

End Sub

Private Function getDogForm(ByVal url As String) As Variant


    Dim forms   As Collection
    Dim form    As Object
    Dim ret()   As Variant
    Dim x       As Long
    Dim details As Object
    Dim dogName As String
    Dim trapNum As String
    Dim dateOfBirth As String
    Dim dogId As String


    With CreateObject("msxml2.xmlhttp")
       .Open "GET", url, False
       .send
       Set details = JSONConvert.ParseJson(.responseText)("details")
    End With
    
    Set forms = details("forms")
    dogName = details("dogInfo")("dogName")
    trapNum = details("dogInfo")("trapNum")
    dateOfBirth = details("dogInfo")("dateOfBirth")
    dogId = details("dogInfo")("dogId")
    
    ReDim ret(1 To forms.Count, 1 To 20)
        
    For Each form In forms
        x = x + 1
        ret(x, 1) = form("shortDate")
        ret(x, 2) = form("trackShortName")
        ret(x, 3) = form("distMetre")
        ret(x, 4) = "[" & form("trapNum") & "]"
        ret(x, 5) = form("secTimeS")
        ret(x, 6) = form("bndPos")
        ret(x, 7) = Right(form("rOutcomeDesc"), 3)
        ret(x, 8) = form("rpDistDesc")
        ret(x, 9) = form("otherDTxt")
        ret(x, 10) = form("closeUpCmnt")
        ret(x, 11) = form("winnersTimeS")
        ret(x, 12) = form("goingType")
        ret(x, 13) = form("weight")
        ret(x, 14) = form("oddsDesc")
        ret(x, 15) = form("rGradeCde")
        ret(x, 16) = form("calcRTimeS")
        ret(x, 17) = dogName
        ret(x, 18) = trapNum
        ret(x, 19) = dateOfBirth
        ret(x, 20) = dogId
    Next form
    
    getDogForm = ret
    
End Function

Is there anything I can do to fix this please?

Many thanks.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Forum statistics

Threads
1,136,331
Messages
5,675,157
Members
419,552
Latest member
jsanjur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top