1

I have curated a file that concatenates form data and generates a JSON code string. Currently, the user has to copy the output into word and then copy that output from word into notepad++ to remove the extra quotations that excel adds when using the Concat function and directly pasting it into notepad++ or seemingly anything non-Microsoft.

However, the next evolution of this template would be a macro button that generates a .json file ready to be pushed into our repository or a local file system. Is this possible? How would you push a single cell into a text file and then save it as a .JSON with a filename generated from the name cell that populates the code?

I've attached the Concat function, the output directly into Notepad++, and the "correct" output that can be achieved by pasting into word first, then Notepad++

The concat function:

=CONCAT("{
    ",CHAR(34),"job",CHAR(34),":
        { 
        ",CHAR(34),"name",CHAR(34)," :  ",CHAR(34),[NAME CELL],CHAR(34),",","
        ",CHAR(34),"domo",CHAR(34)," : {
            ",CHAR(34),"dataPartSize",CHAR(34)," : ", [DATEPARTSIZE CELL] ,CHAR(44),"
            ",CHAR(34),"dataSet",CHAR(34)," :   {
                    ",CHAR(34),"transport",CHAR(34)," : {",,CHAR(34),"type",CHAR(34)," : ",CHAR(34),"API",CHAR(34),", ",CHAR(34),"version",CHAR(34)," : 1},
                    ",CHAR(34),"updateMethod",CHAR(34)," : ",CHAR(34),[UPDATE METHOD CELL],CHAR(34),",
                    ",CHAR(34),"dataProvider",CHAR(34)," : {",CHAR(34),"key",CHAR(34)," : ",CHAR(34),"api",CHAR(34),", ",CHAR(34),"name",CHAR(34)," : ",CHAR(34),"API",CHAR(34),"},
                    ",CHAR(34),"dataSource",CHAR(34)," : {",CHAR(34),"name",CHAR(34)," : ",CHAR(34),[NAME CELL],CHAR(34),", ",CHAR(34),"description",CHAR(34)," : ",CHAR(34),[DESRIPTION CELL],CHAR(34),"},
                    ",CHAR(34),"schemaDefinition",CHAR(34)," :                {
                                            ",CHAR(34),"columns",CHAR(34)," : [
",CONCAT([EXCEL TABLE OF COMBINED COLUMN VALUES FORMATTED WITH DATA TYPE inc. whitespace indentation]),"
","                                            ]
                                            }                             
                    }
        }
    }
}
")

The output, with some dummy values, when directly copying into Notepad++

"{
    ""job"":
        { 
        ""name"" :  ""DEFAULT_NAME"",
        ""domo"" : {
            ""dataPartSize"" : 2000,
            ""dataSet"" :   {
                    ""transport"" : {""type"" : ""API"", ""version"" : 1},
                    ""updateMethod"" : ""REPLACE"",
                    ""dataProvider"" : {""key"" : ""api"", ""name"" : ""API""},
                    ""dataSource"" : {""name"" : ""DEFAULT_NAME"", ""description"" : ""This is the Description""},
                    ""schemaDefinition"" :                {
                                            ""columns"" : [
                                                {""name"" : ""Test1"", ""type"" : ""STRING""},
                                                {""name"" : ""Test2"", ""type"" : ""STRING""},
                                                {""name"" : ""Test3"", ""type"" : ""STRING""},
                                                {""name"" : ""Test_Date"", ""type"" : ""DATE""}
                                            ]
                                            }                             
                    }
        }
    }
}
"

The output when pasted into word and then afterwards, Notepad++, which forms the "correct" JSON output that we currently have to save and name:

{
    "job":
        { 
        "name" :  "DEFAULT_NAME",
        "domo" : {
            "dataPartSize" : 2000,
            "dataSet" :   {
                    "transport" : {"type" : "API", "version" : 1},
                    "updateMethod" : "REPLACE",
                    "dataProvider" : {"key" : "api", "name" : "API"},
                    "dataSource" : {"name" : "DEFAULT_NAME", "description" : "This is the Description"},
                    "schemaDefinition" :                {
                                            "columns" : [
                                                {"name" : "Test1", "type" : "STRING"},
                                                {"name" : "Test2", "type" : "STRING"},
                                                {"name" : "Test3", "type" : "STRING"},
                                                {"name" : "Test_Date", "type" : "DATE"}
                                            ]
                                            }                             
                    }
        }
    }
}
Stuck
  • 75
  • 5
  • If you didn't add those CHAR(34) then you wouldn't have those "extra" quotes? – Tim Williams Jan 17 '23 at 17:59
  • 3
    The typical recommendation here is to use this library when working with JSON: https://github.com/VBA-tools/VBA-JSON That will require you to learn a bit of VBA if you don't already know it, but it seems like that's the direction you're headed anyway. Plenty of examples here on SO - eg. https://stackoverflow.com/questions/59570414/excel-vba-create-json-payload/59571093#59571093 – Tim Williams Jan 17 '23 at 18:03
  • Eg: `=CONCAT("{", """job"":", C4, "}")` – Tim Williams Jan 17 '23 at 18:24
  • How are you validating that the JSON is "correct"? The pasting into Word and Notepad++ are they a roundabout way of getting the line terminators you need? – user10186832 Jan 18 '23 at 07:50
  • 1
    Output the contents of the cell directly to a .TXT file. https://stackoverflow.com/questions/11503174/how-to-create-and-write-to-a-txt-file-using-vba – user10186832 Jan 18 '23 at 07:52
  • 1
    Alternatively create a plain text template with tokens where values can be inserted in a similar way to mail merge. – CDP1802 Jan 18 '23 at 10:50

0 Answers0