VBA-JSON includes a few options for customizing parsing/conversion if needed: json file Set JsonTS = FSO.OpenTextFile( "example.json", ForReading) JsonText = JsonTS.ReadAll JsonTS.Close ' Parse json to Dictionary ' "values" is parsed as Collection ' each item in "values" is parsed as Dictionary Set Parsed = JsonConverter.ParseJson(JsonText) ' Prepare and write values to sheet Dim Values As Variant ReDim Values(Parsed( "values" ).Count, 3 ) Dim Value As Dictionary Dim i As Long i = 0 For Each Value In Parsed( "values" ) Values(i, 0 ) = Value( "a" ) Values(i, 1 ) = Value( "b" ) Values(i, 2 ) = Value( "c" ) i = i + 1 Next Value Sheets( "example" ).Range(Cells( 1, 1 ), Cells(Parsed( "values" ).Count, 3 )) = Values Options json file and load into sheet (Windows-only) ' (add reference to Microsoft Scripting Runtime) ' Dim FSO As New FileSystemObject Dim JsonTS As TextStream Dim JsonText As String Dim Parsed As Dictionary ' Read.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2023
Categories |