Creating Json file from Excel and want to pass null when cell is blank but having difficulty finding-CodePudding

Script is working great and saves json file, but if table cells are blank they are eliminated from the json output. Would love some assistance to solve.

`Private Sub SaveAsJSON_Click()
 
 Set ObjectProperties = CreateObject("Scripting.Dictionary")
 
 For Each c In ActiveSheet.ListObjects(1).HeaderRowRange.Cells
    ObjectProperties.Add c.Column, c.Value

Next

Dim CollectionToJson As New Collection

For Each r In ActiveSheet.ListObjects(1).ListRows
    Set jsonObject = CreateObject("Scripting.Dictionary")
    For Each c In r.Range.Cells
        jsonObject.Add ObjectProperties(c.Column), c.Value
    
    Next
    
    CollectionToJson.Add jsonObject

    
Next
 
 fileSaveName = Application.GetSaveAsFilename(fileFilter:="JSON Files (*.json), *.json")
 
 If fileSaveName <> False Then
    fileNumber = FreeFile
    Open fileSaveName For Output As fileNumber
        Print #fileNumber, JsonConverter.ConvertToJson(CollectionToJson, Whitespace:=2)
        Close fileNumber

End If
End Sub`

CodePudding user response：

Like this:

Dim v As Variant
'...
'...
v = c.Value 
jsonObject.Add ObjectProperties(c.Column), IIf(Len(v) > 0, v, Null)

Input:

Output:

[
  {
    "Col001": "blah",
    "Col002": null,
    "Col003": 44,
    "Col004": "blah",
    "Col005": "blah",
    "Col006": 66,
    "Col007": "blah"
  },
  {
    "Col001": "blah",
    "Col002": "blah",
    "Col003": 67,
    "Col004": "blah",
    "Col005": "blah",
    "Col006": null,
    "Col007": "blah"
  }
]