How to get last element of a JSON object in VBA

[mintz]

{
  "data": {
    "trackings": [
      {
        "tracking_number": "3A5V198427201",
        "shipment_pickup_date": "2016-10-31T23:49:00",
        "tag": "InTransit",
        "title": "3531",
        "checkpoints": [
          {
            "created_at": "2016-11-01T16:09:23+00:00",
            "message": "The electrolic infomation has been received ."
          },
          {
            "created_at": "2016-11-14T17:54:14+00:00",
            "message": "Shipment Ready For Transit."
          },
          {
            "created_at": "2016-11-14T17:54:14+00:00",
            "message": "Shipment Depart From Hub Scan."
          },
          {
            "created_at": "2016-11-15T06:38:28+00:00",
            "message": "A UPS shipping label has been created"
          }
        ]
      }
    ]
  }
}

This is how I get the first checkpoint message:
sMsg = Item("checkpoints")(1)("message")

How do I get the last one? ("A UPS shipping label has been created")

 

[RickXL]

Hi,

You don't say how you are creating your Json Item so I will have to guess.

I have used JsonConverter in the past. Based on that, you might be able to replace the "1" index number with "Item.Count" (without the quotes).

Regards,

 

[mintz]

Hi,

You don't say how you are creating your Json Item so I will have to guess.

I have used JsonConverter in the past. Based on that, you might be able to replace the "1" index number with "Item.Count" (without the quotes).

Regards,

HiRick

That is a JSON response, I simply put the xmlHttp.response text in a string variable called JsonText

JsonText = xmlHttp.responseText

I have just tried:
sMsg = Item("checkpoints")(Item.Count)
but I get the "9 subscript out of range"

 

[John_w]

That is a JSON response, I simply put the xmlHttp.response text in a string variable called JsonText

JsonText = xmlHttp.responseText

But what are you doing with JsonText? You need to parse/convert it using a VBA JSON parser which puts it into a specific data structure which you can then access to extract the elements you need.

 

[mintz]

But what are you doing with JsonText? You need to parse/convert it using a VBA JSON parser which puts it into a specific data structure which you can then access to extract the elements you need.

I'm parsing it like Rick said using the VBA JSON converter:

[COLOR=#000080]Set[/COLOR] ParsedJSONDict = JsonConverter.ParseJson(JsonText)
[COLOR=#000080]Set[/COLOR] dicData = ParsedJSONDict("data")
[COLOR=#000080]Set[/COLOR] dicTrack = dicData("trackings")

    [COLOR=#000080]For Each[/COLOR] Item [COLOR=#000080]In [/COLOR]dicTrack
         sMsg = Item("checkpoints")( *last item* )("message") [COLOR=#008000]' get last checkpoint message[/COLOR]

 

[John_w]

Try this:

    Dim checkpointsColl As Collection
    Set checkpointsColl = dicTrack.item(1).item("checkpoints")
    Debug.Print checkpointsColl.Count
    Debug.Print checkpointsColl.item(1).item("message")  'first checkpoint
    Debug.Print checkpointsColl.item(checkpointsColl.Count).item("message") 'last checkpoint

NB - although it doesn't affect the above code, "trackings" is a Collection, therefore the dicTrack object is a Collection, not a Dictionary as your variable name suggests. Look in the Locals window at run-time and you will see that the last data type of dicTrack is Collection.

 

[chicagocomputerclasses]

JsonText = xmlHttp.responseText

When you debug.print JsonText do you get the same json response text as the one you get in your browser?

 

[RickXL]

Could it be that sMsg = Item("checkpoints")(1)("message") is actually returning the second message and that you need to use:

sMsg = Item("checkpoints")(Item.Count - 1)

to return the last message?

Regards,

 

[mintz]

Try this:

    Dim checkpointsColl As Collection
    Set checkpointsColl = dicTrack.item(1).item("checkpoints")
    Debug.Print checkpointsColl.Count
    Debug.Print checkpointsColl.item(1).item("message")  'first checkpoint
    Debug.Print checkpointsColl.item(checkpointsColl.Count).item("message") 'last checkpoint

NB - although it doesn't affect the above code, "trackings" is a Collection, therefore the dicTrack object is a Collection, not a Dictionary as your variable name suggests. Look in the Locals window at run-time and you will see that the last data type of dicTrack is Collection.

That worked! I was able to access the last message using this:

[COLOR=#000080]For Each[/COLOR] Item [COLOR=#000080]In [/COLOR]dicTrack
  [COLOR=#000080]Set [/COLOR]checkpointsColl = Item("checkpoints")
  sMsg = Item("checkpoints")(checkpointsColl.Count)("message")
  ' further processing
[COLOR=#000080]Next [/COLOR]Item

PS How do I change "trackings" to a Collection instead of a Dictionary?

 

[John_w]

PS How do I change "trackings" to a Collection instead of a Dictionary?

Dim trackingsColl As Collection

and replace instances of dicTrack with trackingsColl.