Export excel data to json

Question

I have a excel workbook 3000-6000 lines in cell "A2"&"B2" is the file name that i need to export as saved file FilenameA2B2.json. In cell "F2" "H2" and few cells in the row is data I need to insert into a for

Insert into a script that looks like

{ "Auto" : { info, info,}{
Info: "F2"(from excel)
Media: "H2"(from excel) } 
more script

Need to create 6000 unique files

Welcome to Stack Overflow! The JSON example that you provided doesn't look like a valid JSON. Could you edit your answer to include more details, and perhaps mention what have you tried so far? Would you prefer having a solution in VBA, or do you have another language of preference? There are nice code examples [in node.js](https://stackoverflow.com/questions/30859901/parse-xlsx-with-node-and-create-json) and [python](https://stackoverflow.com/questions/43973140/convert-xls-to-json-in-python) that could help you out. — Nikolay Shebanov, Aug 28 '20 at 09:14

score 0 · Answer 1 · answered Aug 28 '20 at 10:06

Based purely on the limited information you've given us above, I've drafted up the following which might help you along the way.

Sub createfiles()
    Dim r As Long, ff As Long, myFilename As String
    With ActiveSheet
    For r = 2 To 5 '(5 will do the first 4 rows. Alter this to your range, or detect range using normal methods)
        ff = FreeFile
        myFilename = .Cells(r, 1).Value & .Cells(r, 2).Value
        Open myFilename For Output As #ff
        Print #ff, filetext(.Cells(r, 6).Value, .Cells(r, 8).Value)
        Close ff
    Next
    End With
End Sub

Function filetext(info As String, media As String)
    filetext = "{""Auto"" : {info,info,}{ Info: """ & info & """, Media: """ & media & """} insert more script here}"
End Function

As Nick pointed out though, the filetext string might want tidying up if you're expecting a json reader to parse this later on.

Export excel data to json

1 Answers1