VBA - How to convert JSON to Table ?

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
640
Hi,
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]
    }
  ]
}
nameposition/0position/1location/0location/1
Rixos The Palm Dubai25.121255.1535
Shangri-La Hotel25.208455.2719
Grand Hyatt25.228555.3273

<tbody>
</tbody>
 

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,160
Office Version
365
Platform
Windows
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:

Code:
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
 

Forum statistics

Threads
1,084,749
Messages
5,379,624
Members
401,616
Latest member
YoSquidly

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top