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ᴇʜ
– Pᴇʜ, Commented Sep 27, 2019 at 10:15
PEH, I have checked the values of both JsonObject and JsonText. Both of them contains value — Rick21
– Rick21, Commented Sep 27, 2019 at 10:28
Try to declare item As Variant instead of an Object. But actually I think it should work. — Pᴇʜ
– Pᴇʜ, Commented Sep 27, 2019 at 10:30
@Pᴇʜ I am getting type mismatch error. I have edited the question accordingly. Please help — Rick21
– Rick21, Commented Sep 27, 2019 at 11:13
yeah... and your syntax is expecting a dictionary.....looks like @peh may have resolved so try running that.... — QHarr
– QHarr, Commented Sep 27, 2019 at 13:23

Pᴇʜ · Accepted Answer · 2019-09-27 11:53:09Z

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

Collectives™ on Stack Overflow

Error when parse JSON through VBA-JSON library

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related