Mapping JSON/XML data to Excel cell in VBA?

Question

I'm asking this after some researchs on internet. There's a way to JSON type or XML type files to import Excel sheet but there's no clear descriptions found for my case. I would like to what is my goal;

I have a some type of string data like .txt file like this;

Example Data:

{"vehicle1": {
  "title": "A super red vehicle",
  "weight": "1500 kg",
  "height": "2 m",
    "vehicle1-center_of_gravity": [
      {"x": "2 m"},
      {"y": "-0.5 m"},
      {"z": "1.5 m"}
     "vehicle1-passenger_weights": [
     {"p1": "2 m"},
     {"p2": "-0.5 m"},
     {"p3": "1.5 m"}
    "color": "red",
    ]
  }
}}

I would like to select this file with file dialog and then click to read button now my question comes in here. Is it possible to link/map them with cells for example i have cell named vehicle_title and i want to map this cell with .txt file's "title": "A super red vehicle".

Do you have any experience or idea how can i implement that to my macro.

Regards.

Tim Williams · Accepted Answer · 2018-02-15 07:24:48Z

1

You can use this library

https://github.com/VBA-tools/VBA-JSON

to parse JSON in VBA, but you'll need to do the mapping in code: there's no built-in configuration/wizard for this.

Sub Tester()

    Dim j As Object, json As String, v

    'read from file
    json = CreateObject("scripting.filesystemobject").OpenTextFile( _
                          ThisWorkbook.Path & "\example.txt").ReadAll()

    'import module from: https://github.com/VBA-tools/VBA-JSON
    Set j = JsonConverter.ParseJson(json)

    Set v = j("vehicle1")

    'some attributes...
    Debug.Print v("title")
    Debug.Print v("weight")
    Debug.Print v("vehicle1-center_of_gravity")(1)("x")

End Sub

PS there are a few syntax errors in your JSON sample.

answered Feb 15, 2018 at 7:24

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user8666372 Over a year ago

Thanks, yea i'm not sure is my JSON well documented but you understand me what i mean. I'll test it and feedback. Thanks again.

user8666372 Over a year ago

I test dude it works as you said i should mapped in the code :/

Collectives™ on Stack Overflow

Mapping JSON/XML data to Excel cell in VBA?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related