Excel VBA: Table to Json

I had to look for a possibility to convert a Table (aka ListObject) in Excel to a Json String in Excel VBA. I share here my findings and current solution.

Problem description

Like explained in this post I wanted to convert a Table in Excel into a Json String. (Background is to allow robust property:value string filtering in Jira Description (compared to plain text keyword search based on an Excel Table data).

Something from:

to:

JSON

[{
    "Event ID": "E107",
    "Date": "2020-12-10",
    "Location": "Montgomery",
    "Capacity": "10",
    "Speakers": "Debra Berger"
}, {
    "Event ID": "E108",
    "Date": "2020-12-11",
    "Location": "Montgomery",
    "Capacity": "10",
    "Speakers": "Delia Dennis"
}, {
    "Event ID": "E109",
    "Date": "2020-12-12",
    "Location": "Montgomery",
    "Capacity": "10",
    "Speakers": "Diego Siciliani"
}, {
    "Event ID": "E110",
    "Date": "2020-12-13",
    "Location": "Boise",
    "Capacity": "25",
    "Speakers": "Gerhart Moller"
}, {
    "Event ID": "E111",
    "Date": "2020-12-14",
    "Location": "Salt Lake City",
    "Capacity": "20",
    "Speakers": "Grady Archie"
}, {
    "Event ID": "E112",
    "Date": "2020-12-15",
    "Location": "Fremont",
    "Capacity": "25",
    "Speakers": "Irvin Sayers"
}, {
    "Event ID": "E113",
    "Date": "2020-12-16",
    "Location": "Salt Lake City",
    "Capacity": "20",
    "Speakers": "Isaiah Langer"
}, {
    "Event ID": "E114",
    "Date": "2020-12-17",
    "Location": "Salt Lake City",
    "Capacity": "20",
    "Speakers": "Johanna Lorenz"
}]

Resources

I have found this solution on the Web. 

This is based on https://github.com/VBA-tools/VBA-JSON 

This requires to add the reference to Microsoft Scripting Runtime and import the following module JsonConverter.bas from the zip file. 

Final Code/ Solution

I share in this gist the final code I use.

Compared to the original solution I have updated the code so it is implemented as a function now and returns the String (instead of writing to a file)

Moreover it will only export the visible cells which pretty useful if you only want to convert a filtered part of your Table.

See also

Save Excel Table to JSON File using VBA and VBA-JSON

GitHub - VBA-tools/VBA-JSON: JSON conversion and parsing for VBA