JumboCactuar
Well-known Member
- Joined
- Nov 16, 2016
- Messages
- 785
- Office Version
- 365
- Platform
- Windows
Hi,
i have the following which loads a webpage and extracts the JSON
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
<tbody>
</tbody>
i have the following which loads a webpage and extracts the JSON
Code:
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
Code:
{
"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]
}
]
}
name | position/0 | position/1 | location/0 | location/1 |
---|---|---|---|---|
Rixos The Palm Dubai | 25.1212 | 55.1535 | ||
Shangri-La Hotel | 25.2084 | 55.2719 | ||
Grand Hyatt | 25.2285 | 55.3273 |
<tbody>
</tbody>