VBA - API Call Displayed In Excel
I am trying to show prices of specific cryptocurrencies in an Excel sheet. I am extracting the JSON data from the API of CoinMarketCap - https://api.coinmarketcap.com/v1/ticker/
Ultimately, I am trying to get the price of Ripple (line 16), and then set cell B1 in my Excel sheet to display the price of ripple (line 17).
This is my script, but it is not working for some reason.
Sub test() Dim httpObject As Object Set httpObject = CreateObject("MSXML2.XMLHTTP") sURL = "https://api.coinmarketcap.com/v1/ticker/" sRequest = sURL httpObject.Open "GET", sRequest, False httpObject.Send sGetResult = httpObject.ResponseText Dim oJSON As Object Set oJSON = JsonConverter.ParseJson(sGetResult) If oJSON.Name = "Ripple" Then B1 = oJSON("Ripple")("price_usd") End If End Sub
The API call is successful (I believe), but I get syntax errors etc. Hope anybody is able to help. Thanks in advance
EDIT: This is Microsoft Excel 2010
EDIT 2: It is lines 16 and 17 (respectively
If oJSON.Name... and
B1 = oJSON(... that poses the problem, but I have been unable to solve it/find the error as of now. See comments for Run Time Error etc.
EDIT 3: I believe I have made a mistake in linescomments for Run Time Error etc.
EDIT 3: I believe I have made a mistake in lines 16 and 17 by referring to oJSON and not the item (sItem). However, even after changing this (e.g.
If sItem.Name = "Ripple" Then...), it is still not working.
EDIT 4: I believe I also tagged the excel-cell in the wrong manner. Instead of simply writing
B1 = ..., I am now writing
Range.("B1").Value = ..., which worked in a test.
This modification suggested by @omegastripes works here. The json object is a collection of dictionaries, so you need to treat it as such.
Dim oJSON As Object Set oJSON = JsonConverter.ParseJson(sGetResult) Dim V As Object For Each V In oJSON If V("name") = "Ripple" Then Cells(1, 2) = V("price_usd") Exit For End If Next V
- → Trait 'IlluminateFoundationBusDispatchesJobs' not found
- → Laravel save CSV file error
- → Importing large CSV files in MySQL using Laravel
- → VBA how do I edit cell contents based on contents of two other cells in same row
- → How to work with PHPWord and LaravelExcel in Laravel 5.1 framework?
- → How to generate heading for columns?
- → Reading excel file in Laravel
- → Remove Links on Excel after rendered from table2excel.js
- → Create JSON with through Excel Hierarchy
- → How to download fetch response in react as file
- → Laravel Excel big export. Server error 500
- → Download XLSX file on anchor link click in Laravel 5.2