VBA Access parsing JSON nested array-CodePudding

I am using VBA Access to get data from Google Books for a library database. The code is based on that given in this stackoverflow question.

I am struggling for the right code to allow for a varying number of authors as the information is in a nested array. I would like all of the author names to appear in one TextBox.

I tried:

Form_AddAmendItems.AuthorTextBox.Value = Join(subitem("authors"), ",")

from the link above but that fails to find any result.

I think I need to use UBound and LBound to count the number of authors and then loop through and add each one. But I haven't been able to find an example of how to do that.

Currently as a workaround I can populate the AuthorTextBox with the names of up to 3 authors, which is enough for my needs. But if there are less than 3 authors the error handler message pops up because it hasn't been able to find the requested data.

I am using the VBA-JSON Converter from here.

This is the JSON I would like to parse (from here)

{
  "kind": "books#volumes",
  "totalItems": 1,
  "items": [
    {
      "kind": "books#volume",
      "id": "BT2CAz-EjvcC",
      "etag": "6Z7JqyUtyJU",
      "selfLink": "https://www.googleapis.com/books/v1/volumes/BT2CAz-EjvcC",
      "volumeInfo": {
        "title": "Collins Gem German Dictionary",
        "subtitle": "German-English, English-German",
        "authors": [
          "Veronika Calderwood-Schnorr",
          "Ute Nicol",
          "Peter Terrell"
        ]

And this is my VBA code:

Private Sub FindBookDetailsButton_Click()


'Error handle for Null Strings
    If IsNull(Me.ISBNTextBox) = True Then
        MsgBox "Item ID not specified.", vbExclamation   vbOKOnly, "Error"
    Exit Sub

End If

'Error message if there is no match
On Error GoTo ErrMsg

Dim http As Object, JSON As Object, i As Integer, subitem As Object
Dim ISBN As String
    ISBN = CStr(Me.ISBNTextBox.Value)
Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", "https://www.googleapis.com/books/v1/volumes?q=isbn:" & ISBN, False
    http.send
Set JSON = ParseJSON(http.responseText)
    For Each item In JSON("items")
        Set subitem = item("volumeInfo")
            Form_AddAmendItems.TitleTextBox.Value = subitem("title")
            Form_AddAmendItems.AuthorTextBox.Value = subitem("authors")(1)
            Form_AddAmendItems.PublisherTextBox.Value = subitem("publisher")

'For multiple authors
Set subitem = item("volumeInfo")
    If subitem.Exists("authors") Then
        For Each item2 In subitem("authors")
                Form_AddAmendItems.AuthorTextBox.Value = subitem("authors")(1) & ", " & subitem("authors")(2)
            Next
            For Each item3 In subitem("authors")
                Form_AddAmendItems.AuthorTextBox.Value = subitem("authors")(1) & ", " & subitem("authors")(2) & ", " & subitem("authors")(3)
            Next
        End If
    Next
'To end with success
MsgBox ("Process complete"), vbInformation
Exit Sub

'To end with an error message
ErrMsg:
MsgBox ("No match obtained"), vbCritical

End Sub

CodePudding user response：

An array or collection or dictionary can be looped without knowing that object's limits. So if subitem("authors") is one of those object types, your code could be something like (essentially the code shown in accepted answer for the SO link in your question):

Set subitem = item("volumeInfo")
Form_AddAmendItems.TitleTextBox.Value = subitem("title")
Form_AddAmendItems.PublisherTextBox.Value = subitem("publisher")
If subitem.Exists("authors") Then
    For Each item2 In subitem("authors")
        sA = sA & item2 & ","
    Next
    sA = Left(sA, Len(sA) - 1) 'If you want to remove trailing comma
    If sA <> "" Then Form_AddAmendItems.AuthorTextBox.Value = sA
End If

I discovered that elements in the ISBN download aren't always the same. As an example, with ISBN 0-575-05577-4 the publisher is not provided even though publishedDate is. Might use an If Then condition for each element you want to explicitly address. As a side note, found it interesting that the co-author for that book was not included.