VBA Code to Export Excel Table to Json String

TableToJson.bas

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

See details https://tdalon.blogspot.com/2021/10/excel-vba-table-to-json.html