Excel VBA宏使用iTunes搜索API - 最快的查询方式&解析JSON结果 [英] Excel VBA macro using iTunes search API - fastest way to query & parse JSON results

问题描述

我正在尝试从iTunes查询数据中构建Excel页面。
Angry Birds应用程序的一个例子，我的查询将如下所示：
https://itunes.apple.com/lookup?id=343200656&country=AL 检查阿尔巴尼亚iTunes
https://itunes.apple.com/lookup?id=343200656&country=DZ 检查阿尔及利亚iTunes
... 150多个商店

I am trying to build Excel page from iTunes query data. An example for Angry Birds app my query would look like: https://itunes.apple.com/lookup?id=343200656&country=AL checking Albania iTunes https://itunes.apple.com/lookup?id=343200656&country=DZ checking Algeria iTunes ... 150 more stores

我的问题是执行此查询和解析响应的最有效的方法。
我只知道如何到xmlhttp查询。请启发我作为更好的方式来做到这一点。
我已经阅读了一些关于VB-JSON，Json.net，CDataSet，fastJSON的文档，但无法弄清楚如何开始尝试这些工具。任何人都有更多的VBA代码示例拉JSON或方式来解释这些框架的用法到新手？

My question is the most efficient way to do this query and parse response. I only know how to to xmlhttp query. Please enlighten me as the better way to do this. I have read some documentation for VB-JSON, Json.net, CDataSet, fastJSON, but cannot figure out how to get started trying those tools. Anyone have more VBA code examples pulling JSON or way to explain usage of these frameworks to a newb?

Dim innerHTML As Object
Dim myText As String
JsonCheck = ""
Set innerHTML = CreateObject("Microsoft.XMLHTTP")
With innerHTML
    .Open "GET", iTunesAPI_link, False
    .send
    myText = .responsetext
End With
Set innerHTML = Nothing
If InStr(myText, ":0") = 20 Then   'no results found
    result = "Down"
ElseIf InStr(myText, "Your request produced an error.") = 46 Then 'link error
    result = HTMLCheck(human iTunes link)
Else      'found the app
    result = call function which parses myText for desired fields
Endif

推荐答案

这是一个使用scriptcontrol的基本方法：

Here's a basic approach using the scriptcontrol:

Sub Tester()

    Dim json As String
    Dim sc As Object
    Dim o

    Set sc = CreateObject("scriptcontrol")
    sc.Language = "JScript"

    json = HttpGet("https://itunes.apple.com/lookup?id=343200656&country=AL")

    'some json property names may be keywords in VBA, so replace with
    '  something similar....
    json = Replace(json, """description""", """description_r""")
    Debug.Print json

    sc.Eval "var obj=(" & json & ")" 'evaluate the json response
    'add some accessor functions
    sc.AddCode "function getResultCount(){return obj.resultCount;}"
    sc.AddCode "function getResult(i){return obj.results[i];}"

    Debug.Print sc.Run("getResultCount")

    Set o = sc.Run("getResult", 0)
    Debug.Print o.kind, o.features, o.description_r

End Sub

Function HttpGet(url As String) As String
    Dim oHTML As Object
    Set oHTML = CreateObject("Microsoft.XMLHTTP")
    With oHTML
        .Open "GET", url, False
        .send
        HttpGet = .responsetext
    End With
End Function

有一个解决办法Codo对此问题的回答： Excel VBA：解析的JSON对象循环

There's a worked-out approach in Codo's answer to this question: Excel VBA: Parsed JSON Object Loop

这篇关于Excel VBA宏使用iTunes搜索API - 最快的查询方式&解析JSON结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！