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.

1. Problem description
2. Resources
3. Final Code/ Solution
4. See also
   1. Save Excel Table to JSON File using VBA and VBA-JSON
   2. GitHub - VBA-tools/VBA-JSON: JSON conversion and parsing for VBA

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.

	Sub Test_TableToJson()
	Dim tbl As ListObject
	Set tbl = ActiveSheet.ListObjects(1)
	Json = TableToJson(tbl)
	CopyToClipboard (Json)
	End Sub
	Function TableToJson(tbl As ListObject)
	' Convert input Table to Json String
	' Only Visible/ Unfiltered part is converted
	' See https://tdalon.blogspot.com/2021/10/excel-vba-table-to-json.html
	Set objectProperties = CreateObject("Scripting.Dictionary")
	Dim collectionToJson As New Collection
	For Each r In tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows
	Set jsonObject = CreateObject("Scripting.Dictionary")
	For Each c In r.Cells
	'jsonObject.Add objectProperties(c.Column), c.Value
	jsonObject.Add tbl.HeaderRowRange.Cells(c.Column).Value, c.Value
	Next
	collectionToJson.Add jsonObject
	Next
	TableToJson = JsonConverter.ConvertToJson(collectionToJson, Whitespace:=2)
	End Function

view raw TableToJson.bas hosted with ❤ by GitHub

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