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?

For more info of the API itself, runescape.wikia.com/wiki/Application_programming_interface — Brecht Leben
– Brecht Leben, Commented Jan 23, 2016 at 15:18

Axel Richter · Accepted Answer · 2016-01-23 17:32:58Z

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

answered Jan 23, 2016 at 17:32

Axel Richter

62.5k7 gold badges70 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brecht Leben Over a year ago

Do i just need to copy paste the whole thing from parsing under my sub?

Axel Richter Over a year ago

Download the ZIP file. Extract the JsonConverter.bas. Open Excel and the VBA-editor with your VBA-project. Right click the VBA-project in Project Explorer and click Import File.... Browse to the JsonConverter.bas file and import it. Make sure, you have included a reference to "Microsoft Scripting Runtime" via Tools-References.

Collectives™ on Stack Overflow

API web link contains Json, import to excel using vba

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related