Run time error 10001: error parsing json expecting "" or '''

[guenoon]

Hello,

So I have this issue :

Basically I am trying to paste the data from the json into an excel.

My code is as per below :

Sub TestAPI()
    Dim res() As Variant
    res = Application.Run("GetApiData", "web.api", "api.v2", "ArrayWithHeader")
    For r = 1 To UBound(res, 1)
    Debug.Print "r " & r
        For c = 1 To UBound(res, 2)
        Debug.Print "c " & c
            Cells(5 + r, 5 + c).Value = res(r, c)
        Next c
    Next r
    
    Dim jsonObject As Object
    Dim jsonText As String
    jsonText = Cells(7, 7)
    Set jsonObject = JsonConverter.ParseJson(jsonText)
    
End Sub

And the data looks like this, so jsonText is only the part within the [ ] with these brackets included :

   {
      "region": "EU",
      "data": [
        {
          "region": "EU",
          "sex": "M",
          "name": "Robert"
        },
        {
          "region": "EU",
          "sex": "F",
          "name": "Jessica"
        },
        {
          "region": "EU",
          "sex": "M",
          "name": "Albert"
        },
        {
          "region": "EU",
          "sex": "M",
          "name": "John"
        },
        {
          "region": "EU",
          "sex": "F",
          "name": "Lea"
        },
        {
          "region": "EU",
          "sex": "F",
          "name": "Stella"
        }
      ]
}

So when I run the code I get this error popping : Run time error 10001: error parsing json expecting "" or '''

Could you guys help me how to fix this issue please ?

[RichardWein]

That JSON code isn't valid. There should be a comma between the closing brace of each region and the opening brace of the next region.

VBA Web's error message isn't very helpful here,. You can check JSON syntax online at
https://jsonlint.com/

[guenoon]

Yes sorry I changed manually the data because couldn't post the actual one. But my error is on the '"""' quotes I think so it bugs before.

[guenoon]

Okay I found that what the problem is. The json I am extracting is too large to fit in so it cuts the end that's what causing the error. So my next question is how do I fit all the data in the variable ? If you have any clue I am open to it.

[RichardWein]

How many characters in your JSON data? I believe the limit on a VBA string variable is 2 GB, so it seems unlikely that's the problem. I think I've had JSON responses in the megabytes, with no error (just slow).

[guenoon]

I believe there are around 180k words in the json but I read that the limit in a string variable is 255. So the jsonText string variable can't take all the data and even an excel small is too small. Do you know if there is a way to go around this limit ? Or maybe a better method to do stock the values ?

[RichardWein]

256 is not the limit in VBA. (I think some Excel functions are limited to 255 characters,) I doubt whether the string length is the problem. What makes you think that it is?

[guenoon]

When I paste it in an excel cell, the text is cut and I don't have the end. And if I stock it into a string variable, the JsonConverter.ParseJson displays the same error as in the picture all the way above. So I think that the string variable may not have enough space to stock 180k words. Also when I debug.Print res(2,2) which is supposed to contain all the values, it is also cut in the immediate window.

[RichardWein]

Hi. It doesn't matter that the text is truncated in an Excel cell. What Excel does and what VBA does are quite different. Also, the immediate window has a limited length, so the top of your debug text is probably scrolling off the top of the immediate window and disappearing.

I don't know what your "res" array is. I don't think that's part of the VBA Web code. However, if your JSON text is stored in res(2,2), you can check its length like this:
Debug.Print Len(res(2,2))

Or just type
?Len(res(2,2))
in the immediate window.

I still think the error is caused by a syntax error in the JSON text. Have you tried checking it in an online syntax checker like the one I mentioned (https://jsonlint.com/)? They may not accept such a long text, but it's worth a try.

If you can put your JSON text in a file and attach it to a message here, I'll take a look at it. Or you could attach your Excel file, and I'll try running your VBA code.

[guenoon]

Hello,
The "res" array is typically the variable to stock my data (all the regions, sex, name above). I have tried to Debug.Print Len(res(2,2)) and it returns me 32 767 however there are 180 000 words so it definitely don't take in account all the data.

I also checked in an online syntax checker and it tells me that my JSON text is right.

For the last part, sorry I can't give you the actual JSON and the VBA code won't work as you won't have access to the data. but the code below is exactly the same as I use :

Sub TestAPI()
    Dim res() As Variant
    res = Application.Run("GetApiData", "web.api", "api.v2", "ArrayWithHeader")
    For r = 1 To UBound(res, 1)
        For c = 1 To UBound(res, 2)
            Cells(5 + r, 5 + c).Value = res(r, c)
        Next c
    Next r
    
    Dim jsonObject As Object
    Dim jsonText As String * 60000
    jsonText = res(2,2)
    Set jsonObject = JsonConverter.ParseJson(jsonText)
                
    Dim item As Object
    i = 1
    For Each item In jsonObject
        Cells(16 + i, 7) = item("sex")
        Cells(16 + i, 8) = item("name")
        i = i + 1
    Next
    
End Sub

[RichardWein]

Hi. Thanks for posting that. It's now clear that your string is being truncated to 32,767 characters.

Also, I'm sorry I didn't look at your VBA code before. I only looked at the JSON. Now that I've looked at the code, it seems you are not getting the API data by using VBA-Web. You're just using VBA-JSON to parse the JSON, and getting the API data using a function called "GetApiData". I don't know where this function comes from. But it looks to me as if the problem is in that function.

I have no difficulty getting a VBA function to return strings longer than 32,767 characters. I'm using 64-bit Office, but even if you're using 32-bit Office, I'm pretty sure that's not the problem. I think it's caused by something that function GetApiData is doing.

[guenoon]

Yeah the "GetApiData" function comes from a plugin called EUDA. There is also another function in this plugin, but it gives me the same error. I will try to reach them out to find if they have limits in retrieving data. It is an internal API, that is maybe why they gave me this plugin.

Otherwise do you have another solution to retrieve data from a web API ?

[RichardWein]

Am I right in thinking that you just downloaded the VBA-JSON package? There is an extended package called VBA-Web that handles the whole process of making calls to a Web API. You can find info here:
https://vba-tools.github.io/VBA-Web/docs/
It takes a bit of effort to understand, so don't expect it to be quick! Good luck.

[guenoon]

Yeah you're right, I just downloaded the VBA-JSON package. Okay thank you, I will take a look, if I have questions I will come back to you. Anyway thank you very much for your help !