Excel VBA macro using iTunes search API - fastest way to query & parse JSON results

Question

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

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

score 3 · Answer 1 · edited May 23 '17 at 12:12

3

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

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

edited May 23 '17 at 12:12

Community

1
1

answered Jan 18 '13 at 06:28

Tim Williams

154,628
8
97
125

Thanks for the advice to split web query out into separate function. I have tried playing around with this, but am still lost. I get a crash on 'Debug.Print sc.Run("getResultCount")' It appears you are adding runtime functions to sc Object. I don't understand how kind, features, and description are defined as o Object attribute. – user1989084 Jan 29 '13 at 01:39
The code as posted worked for me - can't say what the issue might be with your version. Consuming JSON in VBA isn't easy, but using js (via the script control) can be one way to do it. Of course, that means you need to understand a little about JSON and js. `Eval` "evaluates" the JSON, creating a js object `obj`. `obj` has a property called `results` which is an array of js objects, each of which has properties such as `kind`, `features`, etc. You can use the accessor functions added to the script control to return a "result" object - you can then access its properties using VBA. – Tim Williams Jan 29 '13 at 06:11
See this [link] (http://stackoverflow.com/questions/5773683/excel-vba-parsed-json-object-loop/19359035#19359035) simple syntax. You can just add a item method via javascript. YOu don't need to go sc.run, just array.item(0). – ozmike Oct 14 '13 at 12:45

score 0 · Answer 2 · answered Apr 03 '14 at 13:43

I had a similar issue with querying Salesforce's REST API and found dealing with JSON through ScriptControl ended up being unmanageable. I used the following library for parsing and converting to JSON and it's worked perfectly for me: https://code.google.com/p/vba-json/.

Dim JSON As New JSONLib
Dim Parsed As Object

Set Parsed = JSON.parse(jsonValue)
Debug.Print Parsed("resultCount")
Debug.Print Parsed("results")(0)

Using that library, I then wrapped up some of the common functionality for making web requests that I think would help you out: https://github.com/timhall/Excel-REST

Using these libraries, your code would look something like the following:

Dim iTunesClient As New RestClient
iTunesClient.BaseUrl = "https://itunes.apple.com/"

Dim Request As New RestRequest
Request.Format = json
Request.Resource = "lookup"
Request.AddQuerystringParam "id", "343200656"
Request.AddQuerystringParam "country", "AL"

Dim Response As RestResponse
Set Response = iTunesClient.Execute(Request)

' => GET https://itunes.apple.com/lookup?id=343200656&country=AL

If Response.StatusCode = 200 Then
    ' Response.Data contains converted JSON Dictionary/Collection
    Debug.Print "Result Count: " & Response.Data("resultCount")
    Dim i As Integer
    For i = LBound(Response.Data("results")) To UBound(Response.Data("results"))
        Debug.Print "Result " & i & ": " & Response.Data("results")(i)
    Next i
Else
    Debug.Print "Error: " & Response.Content
End If

Excel VBA macro using iTunes search API - fastest way to query & parse JSON results

2 Answers2

Linked