Running sample excel to JSON code



  • Reference : https://codingislove.com/excel-json/

    Sorry if I have missed a posting -
    I am trying to run the sample code for exceltojosonfile and exceltojson. In both cases I get a compile error "user-defined type not defined" at "myitem As New Dictionary".
    Any assstance woudl be welcome.
    Thanks
    (This is when using excel 2013.)


  • administrators

    You might have missed this - Add a reference to Microsoft scripting runtime. (Tools > references > select)



  • Hi, just try this. Neither any reference nor any external library is needed:

    Sub Parsing_json()
        Dim http As New XMLHTTP60, itm As Variant
        With http
            .Open "GET", "http://jsonplaceholder.typicode.com/users", False
            .send
            itm = Split(.responseText, "id"":")
        End With
        x = UBound(itm)
    
        For y = 1 To x
            Cells(y, 1) = Split(Split(itm(y), "name"": """)(1), """")(0)
            Cells(y, 2) = Split(Split(itm(y), "username"": """)(1), """")(0)
            Cells(y, 3) = Split(Split(itm(y), "email"": """)(1), """")(0)
            Cells(y, 4) = Split(Split(itm(y), "street"": """)(1), """")(0)
            Cells(y, 5) = Split(Split(itm(y), "suite"": """)(1), """")(0)
            Cells(y, 6) = Split(Split(itm(y), "city"": """)(1), """")(0)
            Cells(y, 7) = Split(Split(itm(y), "zipcode"": """)(1), """")(0)
            Cells(y, 8) = Split(Split(itm(y), "phone"": """)(1), """")(0)
            Cells(y, 9) = Split(Split(itm(y), "website"": """)(1), """")(0)
            Cells(y, 10) = Split(Split(Split(itm(y), "company"": ")(1), "name"": """)(1), """")(0)
            Cells(y, 11) = Split(Split(itm(y), "catchPhrase"": """)(1), """")(0)
            Cells(y, 12) = Split(Split(itm(y), "bs"": """)(1), """")(0)
        Next y
    End Sub

Log in to reply
 

Looks like your connection to Codingislove Forum was lost, please wait while we try to reconnect.