API Web链接包含Json，使用vba导入到excel [英] API web link contains Json, import to excel using vba

问题描述

我正在尝试使用其API的 http://services.runescape.com/m=itemdb_rs/api/catalogue/category.json?category=X 和
我可以在网络浏览器本身中获取json文本：

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

我得到这个vba中的字符串，我如何使用数据？我想使用类别API来知道每个类别中每个字母有多少项目，然后使用它来知道项目API中有多少页面。
有人可以帮我吗？

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?

推荐答案

我不会使用 InternetExplorer 为此。而是使用 XMLHTTP 。

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

要解析 JSON 与 VBA 请参阅 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

这篇关于API Web链接包含Json，使用vba导入到excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！