VBA - How to convert JSON to Table ?
Results 1 to 2 of 2

Thread: VBA - How to convert JSON to Table ?

  1. #1
    Board Regular
    Join Date
    Nov 2016
    Posts
    600
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default VBA - How to convert JSON to Table ?

    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]
        }
      ]
    }
    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

  2. #2
    Board Regular Rijnsent's Avatar
    Join Date
    Oct 2005
    Location
    Utrecht, Holland
    Posts
    1,020
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - How to convert JSON to Table ?

    Hi JumboC,
    if you're using Office365 you could also use the PowerQuery editor, e.g. with this example: https://community.powerbi.com/t5/Pow...on/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
    You can't post attachments here, but you can help me helping you by posting a screen shot directly in your post with any of those tools.
    Otherwise use dropbox/google drive/etc to get your file accross (not preferred). For code, put it inside these tags: [ CODE][/CODE]. Do check the forum rules.
    Finally, please show that you made an effort to solve your problem: Yes, I like to help, but am not going to do your job.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •