VBA - How to convert JSON to Table ?

[JumboCactuar]

Hi,
i have the following which loads a webpage and extracts the JSON

Sub JSON1()
    Dim username As String, password As String
    username = "xxx"
    password = "xxx"
    
Dim xmlhttp As Object, myurl As String


Set xmlhttp = CreateObject("MSXML2.serverXMLHTTP")
myurl = Range("C1")
xmlhttp.Open "GET", myurl, False
xmlhttp.setRequestHeader "Authorization", "Basic " & EncodeBase64(username & ":" & password)
xmlhttp.setRequestHeader "Accept", "application/json"
xmlhttp.Send


'JSON is pasted fully into cell A1
Range("A1") = xmlhttp.responseText


'Now i need to convert JSON to a readable table without knowing the data types.




End Sub

Now once it is loaded into a cell im wanting to convert it into a readable table. Ive looked into using https://github.com/VBA-tools/VBA-JSON but it seems to require you to know data types etc... which can vary within the JSONs i want to parse.

Similar to this is what i need: https://konklone.io/json/
with example json

{
  "markers": [
    {
      "name": "Rixos The Palm Dubai",
      "position": [25.1212, 55.1535],
    },
    {
      "name": "Shangri-La Hotel",
      "location": [25.2084, 55.2719]
    },
    {
      "name": "Grand Hyatt",
      "location": [25.2285, 55.3273]
    }
  ]
}

[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8F8F8]#F8F8F8[/URL] "]
[TH="align: left"]name[/TH]
[TH="align: left"]position/0[/TH]
[TH="align: left"]position/1[/TH]
[TH="align: left"]location/0[/TH]
[TH="align: left"]location/1[/TH]
[/TR]
[TR]
[TD]Rixos The Palm Dubai[/TD]
[TD]25.1212[/TD]
[TD]55.1535[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8F8F8]#F8F8F8[/URL] "]
[TD]Shangri-La Hotel[/TD]
[TD][/TD]
[TD][/TD]
[TD]25.2084[/TD]
[TD]55.2719[/TD]
[/TR]
[TR]
[TD]Grand Hyatt[/TD]
[TD][/TD]
[TD][/TD]
[TD]25.2285[/TD]
[TD]55.3273[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rijnsent]

Hi JumboC,
if you're using Office365 you could also use the PowerQuery editor, e.g. with this example: https://community.powerbi.com/t5/Power-Query/Pull-data-from-a-REST-API-Authentication/td-p/246006
As an alternative: I'm using VBA-JSON in my project and it works like a charm (https://github.com/krijnsent/crypto_vba/). In your code, you could start with:

Dim JsonTxt as String
JsonTxt = xmlhttp.responseText
Set JsonResult = JsonConverter.ParseJson(JsonTxt)
For each M in JsonResult("Markers")
   debug.print M("Name")
Next M

Hope that helps,
Koen