0

I am using this procedure to export a cell range to JSON.

Sub export_json(mysheet As Worksheet, myrange As String)
Dim fs As Object
Dim jsonfile
Dim rangetoexport As Range
Dim rowcounter As Long
Dim columncounter As Long
Dim linedata As String
Dim path As String
Dim fname As String

Set rangetoexport = mysheet.Range(myrange)

path = ThisWorkbook.path & "\"
fname = clean_filename(myrange, "") & ".json"

Set fs = CreateObject("Scripting.FileSystemObject")
Set jsonfile = fs.CreateTextFile(path & fname, True)

linedata = "{""Output"": ["
jsonfile.WriteLine linedata
For rowcounter = 2 To rangetoexport.Rows.Count
    linedata = ""
    For columncounter = 1 To rangetoexport.Columns.Count
        linedata = linedata & """" & rangetoexport.Cells(1, columncounter) & """" & ":" & """" & rangetoexport.Cells(rowcounter, columncounter) & """" & ","
    Next
    linedata = Left(linedata, Len(linedata) - 1)
    If rowcounter = rangetoexport.Rows.Count Then
        linedata = "{" & linedata & "}"
    Else
        linedata = "{" & linedata & "},"
    End If

    jsonfile.WriteLine linedata
Next
linedata = "]}"
jsonfile.WriteLine linedata
jsonfile.Close

Set fs = Nothing
End Sub

However, if a cell value contains double quotes ", the output of JSON file becomes corrupt. Any ideas on how to fix this?

Brhaka
  • 1,622
  • 3
  • 11
  • 31
JoaMika
  • 1,727
  • 6
  • 32
  • 61
  • check [here](https://stackoverflow.com/questions/15637429/how-to-escape-double-quotes-in-json) – AAA Jul 01 '19 at 20:42

1 Answers1

1

You need to escape the double quotes with a slash.

For columncounter = 1 To rangetoexport.Columns.Count
    linedata = linedata & """" & Replace(rangetoexport.Cells(1, columncounter), """", "\""") & """" & ":" & """" _
    & Replace(rangetoexport.Cells(rowcounter, columncounter), """", "\""") & """" & ","
Next

This answer give more details about escaping json. https://stackoverflow.com/a/19176131/7182460

Neil B
  • 2,096
  • 1
  • 12
  • 23