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:
This is one of such chunk containing all the contradictory variations I've tried to describe above:
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: