Im trying to parse Data from a json object with several arrays. This code as an Example:
{
"Version": "1.0",
"I.1": [
{
"MethodenID": "I.1.3",
"Ranking": "1",
"Punktzahl": "20"
},
{
"MethodenID": "I.1.1",
"Ranking": "3",
"Punktzahl": "68"
}
],
"I.2": [
{
"MethodenID": "I.2.2",
"Ranking": "1",
"Punktzahl": "87"
},
{
"MethodenID": "I.2.1",
"Ranking": "2",
"Punktzahl": "67"
}
]}
I want to get only the objects from the inner arrays. I tried the power query tool excel has but ran into some problems having to access every inner list seperately.
After that i tried my luck with vba but had the same problem.
My Question now is: Is it possible at all to do this with vba?
If not is it somehow possible to convert the file into a more readable state?
I tried out the JsonConverter https://github.com/VBA-tools/VBA-JSON already.
Private Sub CommandButton1_Click()
Dim fd As Office.FileDialog
Dim ws As Worksheet
Set ws = Worksheets("Tabelle1")
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.Title = "Select a json file"
.AllowMultiSelect = False
If .Show() Then
ws.Cells(1, 200) = "1"
Filename = (.SelectedItems(1))
Dim content As String
Dim iFile As Integer: iFile = FreeFile
Open Filename For Input As #iFile
content = Input(LOF(iFile), iFile)
'MsgBox (content)
'Parse JSON String
Dim products As Object
Set products = JsonConverter.ParseJson(content)
i = 1
For Each Product In products
ws.Cells(i, 1) = Product("MethodenID")
ws.Cells(i, 2) = Product("Ranking")
ws.Cells(i, 3) = Product("Punktzahl")
i = i + 1
Next
Close #iFile
End If
End With
End Sub
I came up with this from this tutorial: https://www.youtube.com/watch?v=fukOV0hG4eU&t=363s Sadly i dont seem to be able to get to the values in the inner arrays.
Something like this: