0
I had gone through Saving Excel sheet as JSON file, which helped me to convert EXCEL table to JSON but I wish to add some additional fields to this JSON file.
I am asking the following information In first worksheet
- Your ID : A001
- Name : My NAME
- List item : Oct-2018
[My 2nd Sheet]
This information will be common for data entered in 2nd sheet
While creating JSON file I want to Add "Automatically" the following 3 fields (accepted in Sheet 1) in each record for JSON I am creating from Sheet2
- Your ID : A001
- Name : My NAME
- List item : Oct-2018
I am using following VBA code to create JSON
Public Sub xls2json()
savename = "xls2json.json"
Dim wkb As Workbook
Dim wks As Worksheet
Set wkb = ThisWorkbook
Set wks = wkb.Sheets(2)
lcolumn = wks.Cells(4, 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(4, i) 'titles are taken from this row
Next i
json = "["
dq = """"
For J = 5 To lrow ' data picked from this row onwards
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
1It isn't really clear what we're looking at. What is the original worksheet? The description of sheet2 is circular. Where does data get added, field orderwise? What should the resulting file look like? Why not just add the fields in Excel and then convert to JSON? What, exactly, is the difficulty holding you up? – fixer1234 – 2018-09-22T03:39:24.250