API web link contains Json, import to excel using vba

Question

I am trying to get the data of all items of runescape in the grand exchange using its API's http://services.runescape.com/m=itemdb_rs/api/catalogue/category.json?category=X and http://services.runescape.com/m=itemdb_rs/api/catalogue/items.json?category=X&alpha=Y&page=Z I could get the json text in the web browser itself like this:

Sub High_Alch()

Dim IE As New InternetExplorer

Dim url As String

Dim url1 As String

Dim url2 As String

Dim url3 As String

url = "services.runescape.com/m=itemdb_rs/api/catalogue/category.json?category="

IE.Visible = True

For i = 0 To 37


    IE.navigate (url + CStr(i))


Next i

End Sub

How do i get this to a string in vba and how can i use the data? I wanted to use the categories API to know how much items there are for each letter in each category and then use that to know how many page there are in the item API. Can someone help me?

Answer 1

I would not use InternetExplorer for this. Instead I would use XMLHTTP.

For parsing JSONwith VBA see https://github.com/VBA-tools/VBA-JSON.

Sub test()

 Dim httpObject As Object
 Set httpObject = CreateObject("MSXML2.XMLHTTP")

 sURL = "http://services.runescape.com/m=itemdb_rs/api/catalogue/category.json?category="

 For i = 0 To 1 'only category 0 and 1 because of clicking OK in MsgBox ;-)
  sRequest = sURL & i
  httpObject.Open "GET", sRequest, False
  httpObject.send
  sGetResult = httpObject.responseText
  Set oJSON = JsonConverter.ParseJson(sGetResult)
  Set oAlpha = oJSON("alpha")
  For Each oLetter In oAlpha
    sLetter = oLetter("letter")
    lItems = oLetter("items")
    MsgBox "In category " & i & ": Letter " & sLetter & " has " & lItems & " items."
  Next

 Next

End Sub