Error when parse JSON through VBA-JSON library

Question

I'm receiving a

runtime error 13 Type mismatch

at the line Cells(i,1).value=item("fp").

Dont know why the item can't be selected. As you can observe the json file it has "fp" value as "042018"

Sub Jsonread()
Dim FSO As New FileSystemObject
Dim JsonTS As TextStream
Dim jsonText As String
Dim jsonObject As Object
Dim item As variant

Set JsonTS = FSO.OpenTextFile("C:\Users\Abd\retoffline_others.json", ForReading)
jsonText = JsonTS.ReadAll
JsonTS.Close

Dim i As Long

Set jsonObject = JsonConverter.ParseJson(jsonText)

i = 3

For Each item In jsonObject
    Cells(i, 1) = item("fp")
    Cells(i, 2) = item("sply_ty")
    i = i + 1
Next

End Sub

An extract of the json looks like this

{
  "name": "Flip",
  "fp": "042018",
  "filing_typ": "M",
  "gt": 0,
  "cur_gt": 0,
  "b2cs": [
    {
      "csamt": 0,
      "sply_ty": "INTRA"
    },

Probably `jsonObject` is `Nothing` check before using it in the loop: `If jsonObject Is Nothing Then MsgBox "It is Nothing":Exit Sub` • If it is nothing that means your parsing went wrong or your file wasn't read proberly. Check the value of `jsonText` too. — Pᴇʜ, Sep 27 '19 at 10:15
PEH, I have checked the values of both JsonObject and JsonText. Both of them contains value — Rick21, Sep 27 '19 at 10:28
Try to declare `item As Variant` instead of an `Object`. But actually I think it should work. — Pᴇʜ, Sep 27 '19 at 10:30
@Pᴇʜ I am getting type mismatch error. I have edited the question accordingly. Please help — Rick21, Sep 27 '19 at 11:13
Possible duplicate of [Trouble parsing JSON with vba](https://stackoverflow.com/questions/37902688/trouble-parsing-json-with-vba) — Foxfire And Burns And Burns, Sep 27 '19 at 13:03
yeah... and your syntax is expecting a dictionary.....looks like @peh may have resolved so try running that.... — QHarr, Sep 27 '19 at 13:23

score 0 · Answer 1 · answered Sep 27 '19 at 11:53

First of all the JSON that you showed is not valid. It ends with a , but instead of the comma it must be a ]} to make it a valid JSON example:

{
    "name": "Flip",
    "fp": "042018",
    "filing_typ": "M",
    "gt": 0,
    "cur_gt": 0,
    "b2cs": [{
        "csamt": 0,
        "sply_ty": "INTRA"
    }]
}

^{Code 1: Valid JSON example that can be used with the code below.}

Make sure you put only valid JSON data into the parser. If you give invalid data the parser will hang up or in best case throw an error.

Option Explicit

Public Sub Jsonread()
    Dim FSO As New FileSystemObject
    Dim JsonTS As TextStream
    Set JsonTS = FSO.OpenTextFile("C:\Users\Abd\retoffline_others.json", ForReading)

    Dim jsonText As String
    jsonText = JsonTS.ReadAll
    JsonTS.Close

    Dim jsonObject As Object
    Set jsonObject = JsonConverter.ParseJson(jsonText)

    Debug.Print jsonObject("fp")
    Debug.Print jsonObject("b2cs")(1)("sply_ty")
End Sub

^{Code 2: VBA code to parse the JSON from code 1.}

Explanation:

jsonObject("b2cs") represents the following:

"b2cs": [{
    "csamt": 0,
    "sply_ty": "INTRA"
}]

then jsonObject("b2cs")(1) will get the first item in the [] brackets which represents the following:

{
    "csamt": 0,
    "sply_ty": "INTRA"
}

and finally jsonObject("b2cs")(1)("sply_ty") is the item you were looking for, and it will result in:

042018
INTRA

Error when parse JSON through VBA-JSON library

1 Answers1