How to Parse JSON with VBA (MS Access/Excel)

We work with JSON a fair amount (and honestly I can’t say I care for it too much for anything outside of the JavaScript realm), and when it comes to VBA, there’s next to no support for it. Tim Hall has a great set of tools in his github repo, one in specific called VBA-JSON that tends to be a first hit when people have to go searching. I used this for a while myself, until I ran into a case that it fails on, which put me back on the drawing board.

The issue seems to be in the parsing algorithm used: some structures of valid JSON raise errors using the above code (see some examples here – and maybe watch the issue to see if there’s a fix at some point?). Furthermore, there’s a number of other “hand-made” parsers that seem to suffer the same faulty algorithm.

In any case, after numerous hours and at least a dozen different parsers being tested, I found the one that seems to work across the board. As well it should, is it’s tapping into the Microsoft JavaScript Engine to do the work. (credit to StackOverflow member Codo for the original source, as near as I can tell)

Toss the following into a standalone module. Following that is another code example for how to use this.

Module JsonParser:

To use it, do something like this:

Admittedly, it could be easier to use. This effectively parses the values (for any valid JSON – I tested loads of it and gave a presentation at the annual PAUG conference last year without an egg on my face), but working with the values when you’re done is a bit of a headache. Essentially loop through the keys and determine if it’s a Property or Object, and recurse as required. If only JSON has an equivalent query language like XPath for XML!

One important thing to bear in mind is that this method does make use of JavaScript’s Eval() function. That function basically says “take any string and execute it as if it were JavaScript”, which has some significant security implications. Thus, we ought to ensure we’re working with a trusted source if we’re going to be using it (but this holds true for just about any external source, I think).

In any case – while Tim Hall’s interface is a bit more friendly to use and works in most cases, this one works in all cases.

If you’re interested in some more information on how to consume web services with VBA, check out the Web Work with VBA blog post. Have fun!
-jack

(NOTE: NZ() in Excel – the NZ() function is an Access function and not available in excel. If you’re trying to use this code in excel, you’ll need another way to handle nulls. NZ() takes any value and tests it for null, converting it to a number or string if the value is null – google can help)

Share on:

Share on facebook
Share on twitter
Share on linkedin

Reader Interactions

Comments

  1. beashish says

    Hi Jack,

    On processing large amount of data, i am getting “overflow” error at
    GetProperty = ScriptEngine.Run(“getProperty”, JsonObject, PropertyName)
    any idea, how best we can handle this?

    • Jack D. Leach says

      Hi – what’s “large” – a couple hundred objects, or a couple million? If you prefer, email me (or send a link) to a sample file that I can try to reproduce the issue with.

  2. BRJ says

    I was getting an “Subscript out of range” error in the KetKeys function. When I debugged it I found that a key length being returned by GetProperty(KeysObject, “length”) was ZERO, which made the KeysArray dimension NEGATIVE ONE. I am not sure if I have a malformed object but the rest of the data reads fine. I altered your KetKeys to get past this.

    If Length > 0 Then
    ReDim KeysArray(Length – 1)
    Index = 0
    For Each key In KeysObject
    KeysArray(Index) = key
    Index = Index + 1
    Next
    GetKeys = KeysArray
    Else
    ReDim KeysArray(0)
    GetKeys = KeysArray
    End If

    Great code, thanks a billion.

    • Jack D. Leach says

      Thanks for reporting this and the fix. Next time I need to pull the code for a project I’ll update accordingly. I’d be curious to see the some sample data to repro it on my end if you could make it available via email or pastebin or the like.

      • Gary Epstein says

        Hi Rich – thanks for the code module and example. I recognized your name as we have been on some VBA forums before, and remember you as a guy always willing to help others. Well – this is certainly helpful to me and a splendid solution.

        I’ve also run into the same error:

        Error: Run-time error ‘9’: Subscript out of range

        it’s happening on ReDim KeysArray(Length – 1)

        It occurs when the array is empty (no child elements); for example:

        “dentalBenefitDetails” : [ ],

        The fix I added was quite straightforward. In the RecurseProps function, when the length of the ‘nextObject ‘ object is 0, I bypass the call to the RecurseProps method (stop the recursive behavior – where the error occurred).

        Private Function RecurseProps(obj As Object, Optional Indent As Integer = 0) As Object
        Dim nextObject As Object
        Dim propValue As Variant
        Dim keys() As String
        Dim i As Integer

        keys = JsonParser.GetKeys(obj)

        For i = 0 To UBound(keys)

        If JsonParser.GetPropertyType(obj, keys(i)) = jptValue Then
        propValue = JsonParser.GetProperty(obj, keys(i))
        Debug.Print Space(Indent) & keys(i) & “: ” & Nz(propValue, “[null]”)
        Else
        Set nextObject = JsonParser.GetObjectProperty(obj, keys(i))
        Debug.Print Space(Indent) & keys(i)

        ‘ MY EDIT – WHEN THE [] HAS NO CHILDREN BYPASS
        If Len(nextObject) > 0 Then
        RecurseProps nextObject, Indent + 2
        End If
        End If

        Next i

        End Function

  3. Kerry says

    Wow, I’ve gone through many VBA JSON parsers that I’ve found on the internet and this one, by far, is the fastest and cleanest. Thank you for posting this!

  4. Warren_Dyesk says

    I remember your previous question, so it s interesting to see it back again. One question I would have is: let s say you succeed in parsing your JSON in VBA – how would you then use that object in VBA? You note that the JSON structure can be of any type, so how would you navigate the end result in VBA? My first thought might be to create a JScript which would parse the JSON (using eval or even one of the better existing libraries) and then iterate over the structure to produce a nested scripting dictionary-based object to pass back to VBA. What are you doing with your parsed JSON ? Tim Williams Jul 8 ’11 at 18:24

    • Jack D. Leach says

      Hi – this really depends on the data and now deeply it’s nested, how sane the structures are, etc. It’s not so much different from any given XML data you might receive, except we have x-path tools available to navigate through xml data, and unfortunately we don’t have something similar for json. So, manual parsing it is, and all based on your requirements (which is why I didn’t include anything on it here). Good luck

  5. Ryan Way says

    Hi! I’m working on an Access Database for my school library and I need to get a scanner working so that it adds new books that we order through the Google Books API. I’ve gotten the API working fine(responding with the correct string) only it’s in JSON so I’m trying to use your Parser(I couldn’t get VBA-JSON to work as Google Books replies with a pretty advanced JSON string.

    When using this code, I get the error “Object Required” with the “GetKeys” function highlighted.

    Public Sub searchISBN_Change()
    Refresh
    Dim httpReq As Object, response As String
    Dim rootKeys As Dictionary
    Dim info As Object
    Set httpReq = CreateObject(“MSXML2.ServerXMLHTTP”)
    httpReq.Open “GET”, “https://www.googleapis.com/books/v1/volumes?q=isbn:” & Me.searchISBN.value, False
    httpReq.setRequestHeader “Accept”, “application/json”
    httpReq.Send
    response = httpReq.responseText
    Dim fso As Object
    Set fso = CreateObject(“Scripting.FileSystemObject”)
    Dim Fileout As Object
    Set Fileout = fso.CreateTextFile(“S:\Library\Content\BackgroundTEMP\temp.txt”, True, True)
    Fileout.Write response
    Fileout.Close
    JsonParser.InitScriptEngine
    Set info = JsonParser.DecodeJsonString(response)
    Set rootKeys = JsonParser.GetKeys(info)
    Debug.Print rootKeys
    End Sub

    • Ryan Way says

      Just so you know, I forgot to mention, I know that the writing to a file is redundant in this. I was originally using a parser that required using a file(as a trial-run on this three-week long adventure) and just haven’t removed the code yet.

  6. Ryan Way says

    I’m trying to parse a web-response from Google Books API. The trouble is their responses are really advanced, especially for someone like me who is struggling with parsing JSON full stop. I tried to parse it using your parser and have received the edged key list, but all the information on the entire json files seems to be coming back as the key, INCLUDING the information inside of the keys, and there are only 2 keys even available. If I try to access key 3 it returns out of reach. Here’s an example JSON that I’m trying to parse and the response I get in the Debug Console:

    JSON

    {
    "kind": "books#volumes",
    "totalItems": 1,
    "items": [
    {
    "kind": "books#volume",
    "id": "KugbQgAACAAJ",
    "etag": "vFZ18mfheFs",
    "selfLink": "https://www.googleapis.com/books/v1/volumes/KugbQgAACAAJ",
    "volumeInfo": {
    "title": "The Anniversary Man",
    "authors": [
    "Roger Jon Ellory"
    ],
    "publisher": "Orion",
    "publishedDate": "2010",
    "description": "Twenty years ago John Costello's life, as he knew it, ended. He and his beautiful girlfriend, Nadia, were victims of the deranged 'Hammer of God' killer who terrorised New Jersey City throughout the summer of 1984. Nadia was killed instantly. John survived, but withdrew from society, emerging only to work as a crime researcher for a major newspaper. Damaged he may be, but no one in New Jersey knows more about serial killers than John Costello. Then a new spate of murders starts - all seemingly random and unrelated - until John discovers a complex pattern that links them. But could this dark knowledge be about to threaten his life?",
    "industryIdentifiers": [
    {
    "type": "ISBN_10",
    "identifier": "0752883100"
    },
    {
    "type": "ISBN_13",
    "identifier": "9780752883106"
    }
    ],
    "readingModes": {
    "text": false,
    "image": false
    },
    "pageCount": 453,
    "printType": "BOOK",
    "categories": [
    "Fiction"
    ],
    "averageRating": 4.0,
    "ratingsCount": 10,
    "maturityRating": "NOT_MATURE",
    "allowAnonLogging": false,
    "contentVersion": "preview-1.0.0",
    "imageLinks": {
    "smallThumbnail": "http://books.google.com/books/content?id=KugbQgAACAAJ&printsec=frontcover&img=1&zoom=5&source=gbs_api",
    "thumbnail": "http://books.google.com/books/content?id=KugbQgAACAAJ&printsec=frontcover&img=1&zoom=1&source=gbs_api"
    },
    "language": "en",
    "previewLink": "http://books.google.co.uk/books?id=KugbQgAACAAJ&dq=isbn:9780752883106&hl=&cd=1&source=gbs_api",
    "infoLink": "http://books.google.co.uk/books?id=KugbQgAACAAJ&dq=isbn:9780752883106&hl=&source=gbs_api",
    "canonicalVolumeLink": "https://books.google.com/books/about/The_Anniversary_Man.html?hl=&id=KugbQgAACAAJ"
    },
    "saleInfo": {
    "country": "GB",
    "saleability": "NOT_FOR_SALE",
    "isEbook": false
    },
    "accessInfo": {
    "country": "GB",
    "viewability": "NO_PAGES",
    "embeddable": false,
    "publicDomain": false,
    "textToSpeechPermission": "ALLOWED",
    "epub": {
    "isAvailable": false
    },
    "pdf": {
    "isAvailable": false
    },
    "webReaderLink": "http://play.google.com/books/reader?id=KugbQgAACAAJ&hl=&printsec=frontcover&source=gbs_api",
    "accessViewStatus": "NONE",
    "quoteSharingAllowed": false
    },
    "searchInfo": {
    "textSnippet": "The serial killer to end all serial killers is out there and only one person in the whole city knows it."
    }
    }
    ]
    }

    AND here’s the response I get using the parser:

    kind
    books#volumes
    totalItems
    1
    items
    0
    kind: books#volume
    id: nfdejgEACAAJ
    etag: dzfrACqLM/U
    selfLink: https://www.googleapis.com/books/v1/volumes/nfdejgEACAAJ
    volumeInfo
    title: Night Star
    authors
    0: Alyson Noel
    publisher: MacMillan Children's Books
    publishedDate: 2010
    description: Love and Heartbreak belong together ... There are some secrets you're better off not knowing. But once Ever Bloom is given a glimpse of what Damen is keeping from her, she has to find out more-whatever the cost. Their past together is not what she thinks - and only he remembers everything. Until now, when a vengeful friend lets her in on his secret. And it may just push Ever into someone else's arms ...
    industryIdentifiers
    0
    type: ISBN_10
    identifier: 0330528114
    1
    type: ISBN_13
    identifier: 9780330528115
    readingModes
    text: False
    image: False
    pageCount: 302
    printType: BOOK
    categories
    0: Ever (Fictitious character : Noël)
    averageRating: 4.5
    ratingsCount: 3
    maturityRating: NOT_MATURE
    allowAnonLogging: False
    contentVersion: preview-1.0.0
    imageLinks
    smallThumbnail: http://books.google.com/books/content?id=nfdejgEACAAJ&printsec=frontcover&img=1&zoom=5&source=gbs_api
    thumbnail: http://books.google.com/books/content?id=nfdejgEACAAJ&printsec=frontcover&img=1&zoom=1&source=gbs_api
    language: en
    previewLink: http://books.google.co.uk/books?id=nfdejgEACAAJ&dq=isbn:9780330528115&hl=&cd=1&source=gbs_api
    infoLink: http://books.google.co.uk/books?id=nfdejgEACAAJ&dq=isbn:9780330528115&hl=&source=gbs_api
    canonicalVolumeLink: https://books.google.com/books/about/Night_Star.html?hl=&id=nfdejgEACAAJ
    saleInfo
    country: GB
    saleability: NOT_FOR_SALE
    isEbook: False
    accessInfo
    country: GB
    viewability: NO_PAGES
    embeddable: False
    publicDomain: False
    textToSpeechPermission: ALLOWED
    epub
    isAvailable: False
    pdf
    isAvailable: False
    webReaderLink: http://play.google.com/books/reader?id=nfdejgEACAAJ&hl=&printsec=frontcover&source=gbs_api
    accessViewStatus: NONE
    quoteSharingAllowed: False
    searchInfo
    textSnippet: Love and Heartbreak belong together .

    Either I’m doing something wrong, or the parser is parsing the entire JSON string as one item, turning it all into a key without any information inside of it, and then outputting it to the console. If you wish, I can reply to this with the code I’m using to achieve this. Sorry to be such a bother.

    • Jack D. Leach says

      Hi – while I the code I posted is a good method for working with simple, relatively flat structures from a web service or elsewhere, parsing deeply nested and complex data structures in VBA is always going to be a nightmare, even after it’s read into memory using the code in the post. In anything but a relatively simple case, I’d be much inclined to handle this in .NET and use a COM wrapper and reference the resultant dll from the VBA project. .NET can deal with this stuff quite easily (usually), where as it’s an upstream battle with VBA.

Leave a Reply

Your email address will not be published. Required fields are marked *

For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.

Recent articles