This VBA code will work :
Public Sub tojson()
savename = "exportedxls.json"
Dim wkb As Workbook
Dim wks As Worksheet
Set wkb = ThisWorkbook
Set wks = wkb.Sheets(1)
lcolumn = wks.Cells(1, Columns.Count).End(xlToLeft).Column
lrow = wks.Cells(Rows.Count, "A").End(xlUp).Row
Dim titles() As String
ReDim titles(lcolumn)
For i = 1 To lcolumn
titles(i) = wks.Cells(1, i)
Next i
json = "["
dq = """"
For j = 2 To lrow
For i = 1 To lcolumn
If i = 1 Then
json = json & "{"
End If
cellvalue = wks.Cells(j, i)
json = json & dq & titles(i) & dq & ":" & dq & cellvalue & dq
If i <> lcolumn Then
json = json & ","
End If
Next i
json = json & "}"
If j <> lrow Then
json = json & ","
End If
Next j
json = json & "]"
myFile = Application.DefaultFilePath & "\" & savename
Open myFile For Output As #1
Print #1, json
Close #1
a = MsgBox("Saved as " & savename, vbOKOnly)
End Sub
Open VBA /Macros with ALT+F11.
On the left side double click on The worksheet, on the right side paste the code.
Set the variable savename
to the name that you want for the json file and that's all.
4
Googling to "table to json" gives you several solutions, e.g. this
– Máté Juhász – 2017-09-13T07:54:38.137