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
