convert excel to JSON format

[Ehtisham Ali]

Hi Guys,

I have a working macro which converts data to JSON format.

Now I need to modify it for another data, but I am unable to understand the UDF used in the code.

Can anyone please help me.

Existing code works fine for "SizeChart" Sheet but I want to make it working for "Keymaps" sheet.

I am attaching my Macro sheet and expected output for Keymap JSON.

 

[Ehtisham Ali]

Hi All,

While searching I find a code, output is still not expected but it is easier to understand( for me atleast) and modify.

Option Explicit

Sub export_in_json_format()

  Dim fs As Object
  Dim jsonfile
  Dim rangetoexport As Range
  Dim rowcounter As Long
  Dim columncounter As Long
  Dim linedata As String

  ' change range here
  Set rangetoexport = Sheet1.Range("a1:d8")

  Set fs = CreateObject("Scripting.FileSystemObject")
  ' change dir here

  Set jsonfile = fs.CreateTextFile("C:\Users\xx\Desktop\" & "jsondata.json", 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