VBA-Web JSON Multiple Pages

Question

I'm using the VBA-Web Library to process incoming JSON calls in VBA:

Here is a sample small response (1 page only):

You can see at the bottom there's a "TotalPages" returned. Following is my VBA code:

Set Response = Client.Execute(Request)

RoutineErrors = 0

If Response.StatusCode = WebStatusCode.Ok Then

DoCmd.SetWarnings (WarningsOff)

TotalCount = Response.Data("TotalCount")

For i = 1 To TotalCount
    For Each Item In Response.Data("Items")(i)
        Select Case Item
            Case "ClientId"
                ClientId = Response.Data("Items")(i)(Item)
            Case "ClientName"
                ClientName = Response.Data("Items")(i)(Item)
                ClientName = Replace(ClientName, "'", "''")
            Case "TalkTimeInSeconds"
                TalkTimeInSeconds = Response.Data("Items")(i)(Item)
            Case "TransferTimeInSeconds"
                TransferTimeInSeconds = Response.Data("Items")(i)(Item)

                SQL = "INSERT INTO Call_Counts_Temp (ClientId,ClientName,TalkTimeInSeconds,TransferTimeInSeconds,DurationInSeconds,HoldTimeInSeconds) " & _
                      "VALUES ('" & ClientId & "','" & ClientName & "','" & TalkTimeInSeconds & "','" & TransferTimeInSeconds & "'" & _
                      ",'" & DurationInSeconds & "','" & TransferTimeInSeconds & "')"

                DoCmd.RunSQL SQL

            Case "DurationInSeconds"
                DurationInSeconds = Response.Data("Items")(i)(Item)
            Case "HoldTimeInSeconds"
                HoldTimeInSeconds = Response.Data("Items")(i)(Item)
        End Select
    Next
Next

End If

At i = 26 the process errors out with:

Run-time error '9':

Subscript out of range

I'm guessing that's when the loop moves to Page 2. Any ideas how to write the code for this? This is the first time I've worked with a JSON call, so I'm guessing that the way I wrote my code isn't very efficient anyway. Thanks!

It's not clear from the JSON what "TotalPages" and "TotalCount" represent. Does the single JSON response have all of the data, or is it split over multiple responses? Also - you seem to have two loops (the `i` loop and the `For Each`) which both do the same thing? — Tim Williams, Sep 06 '18 at 22:20
Perhaps it is multiple responses. I'm not an expert in this area at all. I'll reach out to the developer of the API to determine. If it is multiple responses, can VBA-WEB handle this? — user10327557, Sep 07 '18 at 23:19

score 0 · Answer 1 · answered Sep 07 '18 at 04:24

You want something more like this:

For Each Item In Response.Data("Items")

    ClientId = Item("ClientId")
    ClientName = Replace(Item("ClientName"),"'","''")
    TalkTimeInSeconds = Item("TalkTimeInSeconds")
    TransferTimeInSeconds = Item("TransferTimeInSeconds")
    DurationInSeconds = Item("DurationInSeconds")
    HoldTimeInSeconds = Item("HoldTimeInSeconds")

    SQL = "INSERT INTO Call_Counts_Temp (ClientId,ClientName,TalkTimeInSeconds, " & _
           "TransferTimeInSeconds,DurationInSeconds,HoldTimeInSeconds) " & _
           "VALUES ('" & ClientId & "','" & ClientName & "','" & _
           TalkTimeInSeconds & "','" & TransferTimeInSeconds & "','" & _
           DurationInSeconds & "','" & HoldTimeInSeconds & "')"

            DoCmd.RunSQL SQL
 Next

Item is a dictionary, so you can address each key directly.

Hi. I tried this way first but it didn't work as all of the tags that are being returned are a level down inside of "Items". — user10327557, Sep 07 '18 at 23:17
OK - I'm more used to this library: https://github.com/VBA-tools/VBA-JSON Using that, `Response.Data("Items")` would be a collection, and the loop's `Item` would be a Dictionary, so you'd be able to access its keys directly — Tim Williams, Sep 07 '18 at 23:45

score 0 · Answer 2 · answered Sep 08 '18 at 03:30

Maybe try eliminating the inner loop. I think that's the most likely place you are getting "Subscript out of range". I think you can access the json elements like this:

For i = 1 To TotalCount

    ClientId = Response.Data("Items")(i)("ClientId")
    ClientName = Replace(Response.Data("Items")(i)("ClientName"), "'", "''")
    TalkTimeInSeconds = Response.Data("Items")(i)("TalkTimeInSeconds")
    TransferTimeInSeconds = Response.Data("Items")(i)("TransferTimeInSeconds")
    DurationInSeconds = Response.Data("Items")(i)("DurationInSeconds")
    HoldTimeInSeconds = Response.Data("Items")(i)("HoldTimeInSeconds")

    SQL = "INSERT INTO Call_Counts_Temp (ClientId,ClientName,TalkTimeInSeconds,TransferTimeInSeconds,DurationInSeconds,HoldTimeInSeconds) " & _
          "VALUES ('" & ClientId & "','" & ClientName & "','" & TalkTimeInSeconds & "','" & TransferTimeInSeconds & "'" & _
          ",'" & DurationInSeconds & "','" & TransferTimeInSeconds & "')"

    DoCmd.RunSQL SQL

Next

Another thing that might be happening is that Response.Data("Items")(26) is missing one or more of the specific elements that you are looking for. Or is empty?

score 0 · Answer 3 · answered Sep 11 '18 at 19:11

0

Thanks you everyone for your assistance. As it turns out, there is a parameter that I can send the page # that I want to return. So, I do a first call to get the number of pages, and then loop through each individually.

answered Sep 11 '18 at 19:11

user10327557

21
2

VBA-Web JSON Multiple Pages

3 Answers3