Display data from dimensional JSON array in VBA, getting "Run-time error '13': Type mismatch" an error message

Question

I'm new to StackOverflow, and I'm struggling with displaying data from a dimensional JSON array in VBA (Excel). Could you please help? Below are the codes I'm using for displaying the data of "ShippingMethod" from the JSON.

Example of JSON: As you are able to see the "Data" is the first object. "Shipments" and "DisplayItems" are the array of "Data".

Also, there are multiple objects inside the "Shipments" array. For example "ShippingMethod" and "ShippingName", and my goal is to display the data of these objects. ("LC") and ("No Charge - Lowest Cost 3-5 Day Delivery")

Here are my codes: First method:

 Dim Json1 As Dictionary
 Set Json1 = JsonConverter.ParseJson(strResponse3)
 home.Activate

 home.Cells(x, 2) = Json1("Data")("Shipments")(1)("ShippingMethod")

Second method:

Dim Json1 As Dictionary
 Set Json1 = JsonConverter.ParseJson(strResponse3)
 home.Activate

x = 42

    For Each Item In Json1("Data")("Shipments")
    home.Cells(x, 2) = Item("ShippingMethod")
    
    x = x + 1
    
    Next

I'm not able to display the data of these "ShippingMethod" and "ShippingName" objects as I'm getting an error message "Run-time error '13': Type mismatch" from the VBA. NOTE: I'm using the same method on another JSON XML, it's able to retrieve the data from the dimensional JSON array without any issues.

Update on my codes(7/8)

Dim Json1 As Dictionary, x As Long, y As Long
Dim shipments

home.Activate

x = 42
  
Set Json1 = JsonConverter.ParseJson(strResponse3)

  
Set shipments = Json1("Data")("Shipments")  '<-- Getting error "Run-time error '13': Type mismatch"'

For y = 3 To shipments.count
    home.Cells(x, 2) = shipments(y)("ShippingMethod")
    
    x = x + 1
    
Next

Error message_screenshot for Set shipments = Json1("Data")("Shipments") The VBA highlight this line of code after selecting "Debug" button.

Note: try the below debug.print. However, the data returned as "0".

Debug.Print VarType(Json1("Data")("Shipments"))

Update on my codes(7/21)

Dim Json1 As Dictionary, x As Long, y As Long Dim FSO, ts, s As String Dim shipments

home.Activate

 ' write json to file
Set FSO = CreateObject("Scripting.FileSystemObject")
s = ThisWorkbook.Path & "\strResponse3.json"
Set ts = FSO.CreateTextFile(s)
ts.Write strResponse3
ts.Close
MsgBox Len(strResponse3) & " bytes written to " & s

x = 42
 
Set Json1 = JsonConverter.ParseJson(strResponse3)

Debug.Print "Json1", VarType(Json1)
Debug.Print "Json1(Data)", VarType(Json1("Data"))
Debug.Print "Json1(Data)(PriceSummary)", VarType(Json1("Data")("PriceSummary"))
Debug.Print "Json1(Data)(Shipments)", VarType(Json1("Data")("Shipments"))
Debug.Print "Json1(Data)(DisplayItems)", VarType(Json1("Data")("DisplayItems"))

home.Cells(x, 1) = Json1("Data")("Orders")("ShipmentId")

Set shipments = Json1("Data")("Shipments")
'home.Activate

For i = 1 To shipments.count
    Cells(x, 2) = shipments(i)("ShippingMethod")
    x = x + 1
Next

In the Txt output file, I noticed it only returning the data of "Data":{"PriceSummary":{,. Please see the beginning of the data TXT OUTPUT screenshot and compare it with the JSON screenshot. Also, please check the ending of the data TXT OUTPUTand compare it with the JSON screenshot. The data only contain whenever inside the "PurchaseSummary" and there is no data with the arrays "Shipment" and "DisplayItems".

Here's the Debug.Print screenshot. No data returns for Shipments and DisplayItems. I strongly believe there is something wrong with the JSON. (Kindly please refer back to the very first screenshot for the JSON pattern)

Debug.Print result: Json1 9

Json1(Data) 9

Json1(Data)(PriceSummary) 9

Json1(Data)(Shipments) 0

Json1(Data)(DisplayItems) 0

FYI JSON and XML are not the same thing - that are both useful data exchange formats, but there is no "JSON XML" so I'd avoid using that term. — Tim Williams
– Tim Williams, Commented Jul 7, 2021 at 21:28
Thank you @Tim Williams for your information, I will avoid using the same term together in the future. — Raiden Choong
– Raiden Choong, Commented Jul 8, 2021 at 5:44

CDP1802 · Accepted Answer · 2021-07-09 10:06:44Z

0

Update - added JSON file export and test data.

Option Explicit
Sub test()
    Dim Json1 As Dictionary, x As Long, i As Long
    Dim shipments
    Dim FSO, ts, strResponse3 As String, s As String

    strResponse3 = "{""Data"" : {" & _
    """Shipments"":[" & _
    "{""ShippingMethod"":""LC""}," & _
    "{""ShippingMethod"":""LC""}," & _
    "{""ShippingMethod"":""LC""}" & _
    "]}}"

    ' write json to file
    Set FSO = CreateObject("Scripting.FileSystemObject")
    s = ThisWorkbook.Path & "\strResponse3.json"
    Set ts = FSO.CreateTextFile(s)
    ts.Write strResponse3
    ts.Close
    MsgBox Len(strResponse3) & " bytes written to " & s

    Set Json1 = JsonConverter.ParseJson(strResponse3)
    Debug.Print "Json1", VarType(Json1)
    Debug.Print "Json1(Data)", VarType(Json1("Data"))
    Debug.Print "Json1(Data)(Shipments)", VarType(Json1("Data")("Shipments"))

    Set shipments = Json1("Data")("Shipments")
    'home.Activate
    x = 42
    For i = 1 To shipments.Count
        Cells(x, 2) = shipments(i)("ShippingMethod")
        x = x + 1
    Next
    
End Sub

edited Jul 9, 2021 at 10:06

answered Jul 7, 2021 at 11:09

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

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

14 Comments

Raiden Choong Over a year ago

Thank you so much for your help! I tried to code using the same but unfortunately still getting the "Type mismatch" errors while debugging on the line: Set shipments = Json1("Data")("Shipments") <-- this line is getting an error.

Tim Williams Over a year ago

That code works for me with your posted JSON, so maybe you're not getting the response you expect?

Raiden Choong Over a year ago

Set shipments = Json1("Data")("Shipments") this line is getting an error when debugged. Strange, the code works on calling data from other arrays, but not on the "Shipment" & "DisplayItems" from the posted JSON.

Raiden Choong Over a year ago

I've just posted my updated code at the bottom of my post, please refer to "Update on my codes(7/8)" .

CDP1802 Over a year ago

@Raiden What do you get with Debug.print VarType(Json1("Data")("Shipments"))

|

Collectives™ on Stack Overflow

Display data from dimensional JSON array in VBA, getting "Run-time error '13': Type mismatch" an error message

1 Answer 1

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related