Sub excelToJsonFileExample()
Dim excelRange As Range
Dim jsonItems As New Collection
Dim jsonDictionary As New Dictionary
Dim jsonFileObject As New FileSystemObject
Dim jsonFileExport As TextStream
Dim i As Long
Dim cell As Variant
Set excelRange = Cells(1, 1).CurrentRegion
For i = 2 To excelRange.Rows.Count
jsonDictionary("id") = Cells(i, 1)
jsonDictionary("name") = Cells(i, 2)
jsonDictionary("username") = Cells(i, 3)
jsonDictionary("email") = Cells(i, 4)
jsonDictionary("street") = Cells(i, 5)
jsonDictionary("suite") = Cells(i, 6)
jsonDictionary("city") = Cells(i, 7)
jsonDictionary("zipcode") = Cells(i, 8)
jsonDictionary("phone") = Cells(i, 9)
jsonDictionary("website") = Cells(i, 10)
jsonDictionary("company") = Cells(i, 11)
jsonItems.Add jsonDictionary
Set jsonDictionary = Nothing
Next i
‘change the path below to save where you desire
Set jsonFileExport = jsonFileObject.CreateTextFile("C:\Users\username\Desktop\jsonExample.json", True)
jsonFileExport.WriteLine (JsonConverter.ConvertToJson(jsonItems, Whitespace:=3))
End Sub
参考
http://excelerator.solutions/2017/08/24/convert-excel-to-json-using-vba/