Parsing Nested Arrays using VBA and JSON

Question

I have a JSON I am trying to parse in VBA. I have successfully parsed out the array of "offers". Inside the array of "offers" is another array "prices""USD".

The problem is that not every "offers" object has the "USD" array. I am trying to create an object that I can make a table/sheet with but I can't even get the objects to print in debug mode. This works but fails because not every Dict OfferDetails contains the "USD" object.

What I would like to do is be able to print the string and if the "USD" object is missing just skip it and only print the ones that have the "USD". I have tried the IsMissing (in code) but it fails when it hits the missing "USD" object.

Any idea how I can get this string with the "USD" values? Note that the "USD" is an array and contains several objects, but I don't know how to address them either. Ideally I would like to parse out the "USD" the same way I did the "offers". I am totally lost as I am not very good in VBA

This is a working script with a valid web JSON.

  Sub getJSONEP_lib_working()
      'Need the JsonConverter found here https://github.com/VBA-tools/VBA-JSON
      'Need the Microsoft Scripting Runtime 

      Dim Parsed As Dictionary
      Dim Item As Dictionary
      Dim OfferDetails As Dictionary
      Dim Price As Dictionary
      Dim USD As Dictionary

              URL = "http://wraymac.com/JSON/example1.json"
              url2 = "[{" & """mpn""" & ":" & """41202""" & "}]"

              Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
              MyRequest.Open "GET", URL
              MyRequest.Send

              JsonString = MyRequest.ResponseText

              Dim json As Object
              Set json = JsonConverter.ParseJson(JsonString)


      Set Parsed = JsonConverter.ParseJson(MyRequest.ResponseText)

         For Each Item In Parsed("results")(1)("items")
           For Each OfferDetails In Item("offers")


      'I tried this it doesn't work, it fails when it finds a non existent "USD"
            If Not IsMissing(OfferDetails("prices")("USD")(1)(1)) Then
            Debug.Print OfferDetails("prices")("USD")(1)(1)
            Else
            Debug.Print "Missing"
            End If


      x = Item("mpn") & "   " & "sku" & " - " & OfferDetails("sku") & "," & "UID" & " - " & OfferDetails("seller")("uid") & "   " & OfferDetails("moq") & "packaging" & " = " & OfferDetails("packaging") & "  " & OfferDetails("seller")("name") & "  " & Item("manufacturer")("name")
      Debug.Print x

      'This works but fails because not every Dict OfferDetails contains the "USD" object
      'x = Item("mpn") & "   " & "sku" & " - " & OfferDetails("sku") & "," & "UID" & " - " & OfferDetails("seller")("uid") & "   " & OfferDetails("moq") & "packaging" & " = " & OfferDetails("packaging") & "  " & OfferDetails("seller")("name") & "  " & Item("manufacturer")("name")& "  "&OfferDetails("prices")("USD")(1)(1)

       Next OfferDetails
           Next

      End Sub

Can you please show us the actual JSON string being returned? — SierraOscar
– SierraOscar, Commented Nov 5, 2016 at 22:48
It is very long, the reason I didn't post in my message. So you can get it here... wraymac.com/JSON/example1.json "wraymac.com/JSON/example1.json" If that doesn't work I could post it — Wraymac
– Wraymac, Commented Nov 5, 2016 at 23:45
Consider going here and formatting your JSON a bit... jsonprettyprint.com/json-pretty-printer.php — Tim Williams
– Tim Williams, Commented Nov 6, 2016 at 0:58
I use json.parser.online.fr/beta, just cut and paste, shows everything, types and indexes, — Wraymac
– Wraymac, Commented Nov 6, 2016 at 2:04

Tim Williams · Accepted Answer · 2016-11-06 03:46:44Z

7

You want to use the Dictionary's Exists method:

Set Parsed = JsonConverter.ParseJson(MyRequest.ResponseText)

For Each Item In Parsed("results")(1)("items")
    For Each OfferDetails In Item("offers")

            If OfferDetails("prices").Exists("USD") Then
                Debug.Print OfferDetails("prices")("USD").Count & " items:"
                Debug.Print "-----------------"
                For Each x In OfferDetails("prices")("USD")
                    Debug.Print x(1), x(2)
                Next x
                Debug.Print "-----------------"
            Else
                Debug.Print "No USD"
            End If

     Next OfferDetails
 Next

The converter parses objects ({}) to dictionaries, and arrays ([]) to collections, so you can use Count to determine the number of items in each of those types of object.

edited Nov 6, 2016 at 3:46

answered Nov 6, 2016 at 1:17

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

1 Comment

Wraymac Over a year ago

Works, thanks. Now if I can figure out how to handle all the USD entries, they all have different values. I guess I could reference each one in code, but they change. For example offers (0) or (1)vba has four entries and offers (1) (2)vba has eight . but this works very well , again thank you

Collectives™ on Stack Overflow

Parsing Nested Arrays using VBA and JSON

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related