Type Mismatch (Error 13) in Excel using Excel Json Parser VBA

Question

Getting Type Mismatch (Error 13) while using Json PArser in Excel MAcro

Below are the code :

Sub getJsonValue()
    Dim FSO As New FileSystemObject
    Dim JsonTS As TextStream
    Set JsonTS = FSO.OpenTextFile("C:\Users\Card_Link.json", ForReading)
    JsonText = JsonTS.ReadAll
    JsonTS.Close
    Set Json = ParseJson(JsonText)
    Set JsonRows = Json("rows")
    i = 2
    For Each Item In Json
         Sheet5.Cells(i, 1).Value = Item("name")
        'Sheet5.Cells(i, 2).Value = Item("results")("name")
        'Sheet5.Cells(i, 3).Value = Item("results")("responsecode")
        i = i + 1
    Next
    MsgBox ("complete")
End Sub

Getting error on this statement Sheet5.Cells(i, 1).Value = Item("name")

Can someone please help me to resolve this.

Thanks RJ

Vityata · Accepted Answer · 2017-08-16 11:55:49Z

1

Without having any experience with ParseJson, try one of these:

Sheet5.Cells(i, 1).value = item

Sheet5.Cells(i, 1).value = item(0)

Sheet5.Cells(i, 1).value = item.Name

If still none of these works, try like this:

For Each item In Json
    Stop
     Sheet5.Cells(i, 1).value = item("name")
    'Sheet5.Cells(i, 2).Value = Item("results")("name")
    'Sheet5.Cells(i, 3).Value = Item("results")("responsecode")
    i = i + 1
Next

On the stop, select item, press Shift+F9 and see what do you have in item.

edited Aug 16, 2017 at 11:55

answered Aug 16, 2017 at 11:50

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user8472243 Over a year ago

Thanks for your reply. Sheet5.Cells(i, 1).value = item - this statement is working but I want to get the subitem values. I tried item(0), item.Name and item(Name) but nothing is working. Can you please help me

Vityata Over a year ago

@user8472243 - have you tried Shift + F9? What happened?

user8472243 Over a year ago

Sorry for late response. In "Item", I am getting correct value now but when I am going to get the next level value like item.name or item(name). Its showing error - 424 (Run time Error) says "Object Required" on this statement - Sheet5.Cells(i, 2).Value = Item.Name

Vityata Over a year ago

@user8472243 - how about item("name") or item("Name")?

Collectives™ on Stack Overflow

Type Mismatch (Error 13) in Excel using Excel Json Parser VBA

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related