• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Can't call the corresponding values using it's keys

shahin

Active Member
While working with a json api, when I performed a search in google, I found this very link "https://www.mrexcel.com/forum/excel-questions/898899-json-api-excel.html" where there is a guy named Kyle123 given a very effective solution which is very likely to the problem I was facing. I know here in this forum there are few posts against which Marc L also provided similar type of solutions.

However, I have got few questions about the below scripts (it is working other than row1 and row3 because they have got contradictory names):

1. How this line "scriptControl.Eval("(" + .responseText + ")")" and this ".abort" work? Any hint or link will be very helpful.

2. There are few names that are contradictory such as ,ID, Rank, Status. They perhaps are the built-in names of vba so i can't use them to parse their corresponding values. What is the workaround? I tried with ".getAttribute("id")" but that did not work either.

3. Sometimes, there are few json keys look like "available_supply","available-supply","available-supply-qty". How to use them to scrape their corresponding values? Because when I try to use "item.available-suply" it becomes "item.available - suply" in the vbe and no longer works. (problem 2 and 3 are almost identical)


This is the script:
Code:
Sub GetData()
    Dim Http As New XMLHTTP60, scriptControl As Object
    Dim post As Object, results As Object, R&

    Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
    scriptControl.Language = "JScript"

    With Http
        .Open "GET", "https://api.coinmarketcap.com/v1/ticker/", False
        .send
        Set results = scriptControl.Eval("(" + .responseText + ")")
        .abort
        For Each post In results
            R = R + 1: Cells(R, 1) = post.id  'check this out. The "id" becomes "ID" when pasted in the vbe
            Cells(R, 2) = post.name
            Cells(R, 3) = post.Rank   'check this out as well "Rank"
            Cells(R, 4) = post.available_supply
        Next post
    End With
End Sub

This is one of such chunk containing all the contradictory variations I've tried to describe above:

Code:
    {
        "id": "bitcoin",
        "name": "Bitcoin",
        "symbol": "BTC",
        "rank": "1",
        "status": "substandard",
        "available-supply-qty": "113296036796",
        "available_supply": "17094425.0",
        "total-supply": "17094425.0",
    }
 
Last edited:
Thanks Marc L for your provided link. When I first read it, I just did the copy paste and that is why I did not understand few things clearly. However, I've been suffering from this "calling key" issues for several days, you just let me get rid of this pain by showing "CallByName". Thanks a million for that. One last thing : what does ".abort" do in my above script? Without that ".abort" there is no problem parsing those values , though. Thanks again.
 
Last edited:
Found another bit of chunk where I can't find any way to use "CallByName" especially when I wanna parse the value of "edge-city-code".
Code:
Sub GetData()
    Dim Http As New XMLHTTP60, scriptControl As Object
    Dim results As Object

    Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
    scriptControl.Language = "JScript"

    With Http
        .Open "GET", "https://usa.cloud.netacuity.com/webservice/query?u=04842bc1-ecc8-4db1-aeec-6a7708559ff2&json=true", False
        .send
        Set results = scriptControl.Eval("(" + .responseText + ")")
        .abort
   
       [A1] = CallByName(results, "response", VbGet)  'using CallByName and found it working
        [A2] = results.response.ip ''it's okay if i go like this
        [A3] = results.response.edge - city - code ''take a look here (can't find any way to use "CallByName" here
    End With
End Sub

And the chunk of json:
Code:
{"response": {
"edge-country-code" : "50",
"edge-region-code" : "25175",
"mcc" : "0",
"edge-region" : "c",
"edge-postal-conf" : "30",
"edge-postal-code" : "1000",
"edge-country-conf" : "99",
"edge-region-conf" : "90",
"edge-gmt-offset" : "+600",
"edge-timezone-name" : "asia/dhaka",
"edge-country" : "bgd",
"edge-city" : "dhaka",
"edge-city-conf" : "90",
"edge-longitude" : "90.4071",
"mnc" : "0",
"mobile-carrier" : "?",
"ip" : "45.118.62.171",
"edge-two-letter-country" : "bd",
"edge-metro-code" : "-1",
"edge-continent-code" : "4",
"edge-city-code" : "14290",
"edge-area-codes" : "?",
"edge-latitude" : "23.7098",
"edge-in-dst" : "n",
"edge-conn-speed" : "broadband"
}}
 
Okay, It seems I've found out the way it should be:
Code:
Sub GetData()
    Dim Http As New XMLHTTP60, scriptControl As Object
    Dim results As Object, post As Object

    Set scriptControl = CreateObject("ScriptControl")
    scriptControl.Language = "JScript"

    With Http
        .Open "GET", "https://usa.cloud.netacuity.com/webservice/query?u=04842bc1-ecc8-4db1-aeec-6a7708559ff2&json=true", False
        .send
        Set results = scriptControl.Eval("(" + .responseText + ")")
        .abort
      
        Set post = CallByName(results, "response", VbGet)
        [A3] = CallByName(post, "edge-city-code", VbGet) 'this is the fix
    End With
End Sub
 
Last edited:
Back
Top