今回はVBAを使って不要な列をシート状には出さずに非表示にしたいと思います。
USDのデータを非表示にしてJPYのデータを左のほうの列に移動させます。
Sub Crypto_JSON_In_Excel()
Dim CryptoCurrencyJSON As Object, CryptoCurrencyNode
Dim WinHttpReq As Object, TickerJSON As String
Dim tickerSheet As Worksheet, arr(10) As Variant
Set tickerSheet = ThisWorkbook.Sheets(1)
arr(1) = "name"
arr(2) = "symbol"
arr(3) = "price_jpy"
arr(4) = "price_btc"
arr(5) = "24h_volume_jpy"
arr(6) = "market_cap_jpy"
arr(7) = "available_supply"
arr(8) = "percent_change_1h"
arr(9) = "percent_change_24h"
arr(10) = "percent_change_7d"
Set WinHttpReq = CreateObject("MSXML2.XMLHTTP")
WinHttpReq.Open "GET", "https://api.coinmarketcap.com/v1/ticker/?convert=JPY&limit=10", False
WinHttpReq.send
TickerJSON = WinHttpReq.responseText
Set WinHttpReq = Nothing
Set CryptoCurrencyJSON = ParseJson(TickerJSON)
For i = 1 To 10
tickerSheet.Cells(1, i) = arr(i)
Next i
trow = 2
For Each CryptoCurrencyNode In CryptoCurrencyJSON
For i = 1 To 10
tickerSheet.Cells(trow, i) = CryptoCurrencyNode(arr(i))
Next
trow = trow + 1
Next
End Sub
●実行結果
