VBA Access parsing JSON nested array

Question

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

Is textbox bound to a Long Text field? See if this helps https://stackoverflow.com/questions/48809465/parsing-json-array-via-vba. Instead of Debug.Print you would concatenate a string variable then save that string to textbox. — June7, Sep 10 '22 at 19:52
I tried to implement the VBA-JSON converter code but get too many compile errors so giving up. Yes, I have Microsoft Scripting Runtime library activated. — June7, Sep 10 '22 at 20:09
@June7 I tried that link but couldn't get the solution given to work for me. Maybe because the data they are parsing in that link is in {} so it's object rather than the [] array data I am trying to parse? The textbox isn't bound to a Long Text field, but I'm not sure that's needed as the 255 characters of a normal Text field will be more than sufficient. Unless there's another advantage of a Long Text field? — Bookworm, Sep 10 '22 at 22:23
I referenced that link because title specified parsing an array. Could you set a VBA array object to that JSON array? Loop the VBA array to concatenate array elements. I am able to use string manipulation to parse data but that has its own frustrations. Disappointing I could not get the JSON converter to work. No advantage to Long Text field as long as you stay under 255 characters. — June7, Sep 10 '22 at 23:22

June7 · Accepted Answer · 2022-09-12T02:31:01.827

0

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) '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.

edited Sep 12 '22 at 02:31

answered Sep 11 '22 at 00:46

June7

19,874
8
24
34

That is great - thank you so much. I made a tiny change adding a space after the comma `","` to `", "` and so changed `sA = Left(sA, Len(sA) - 1)` to `sA = Left(sA, Len(sA) - 2)`. I hadn't realised the potential of the Left function. I had thought of it as extracting from the text by only fetching the relevant part, ignoring the rest. But I think it must remove it, hence why this works for 3+ authors as each one is moved up to item2 as the one before is fetched. – Bookworm Sep 11 '22 at 07:30
Don't quite follow that. Left function has nothing to do with building string within loop. It is outside loop and does extract relevant part and ignores rest by truncating string to drop trailing comma. Works same for 1 or more than 1 authors. Regardless, glad it works for you. – June7 Sep 11 '22 at 17:48

VBA Access parsing JSON nested array

1 Answers1