Object Vba read items

Question

I can not read Movie Object, I don't know Why??, I'm trying to return an JSOn API to my sheet in excel, buy I can not read the Movie Object.

Sub getData()

Dim Movie As Object
Dim R As Object
Dim scriptControl As Object

Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
scriptControl.Language = "JScript"

With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", "https://api.coinmarketcap.com/v1/ticker/shift/", False
    .send
    Set R = scriptControl.Eval("(" + .responsetext + ")")
    .abort
    With Sheets("API")
        For Each Movie In R
            MsgBox (Movie.Name)
            .Cells(1, 2).Value = Movie.price_btc
            .Cells(1, 3).Value = Movie.price_usd
            .Cells(1, 4).Value = Movie.Rank
        Next Movie
    End With
End With

End Sub

I create a Sheet named API to return values but I don't know how retrive value elements in Movie Object

You can check in debug than Movie contain data but when I try to put in cell send an error:

Possible duplicate of [Parsing JSON in Excel VBA](http://stackoverflow.com/questions/6627652/parsing-json-in-excel-vba) — Comintern, Dec 06 '16 at 17:27

Tim Williams · Accepted Answer · 2016-12-06T18:08:47.847

Your problem is that certain properties of javascript objects cannot directly be accessed in VBA if their name matches a VBA keyword (or possible some other "reserved" term). "name" is an example - you will see you cannot write Movie.name without the case getting switched to Movie.Name

Here's one approach which uses javascript to access the properties, but you might be better off using something like VBAjson (https://github.com/VBA-tools/VBA-JSON) since it offers a better "multi-purpose" approach.

Sub getData()

    Dim Movie As Object
    Dim R As Object
    Dim sc As Object, x

    Set sc = CreateObject("MSScriptControl.ScriptControl")
    sc.Language = "JScript"

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://api.coinmarketcap.com/v1/ticker/shift/", False
        .send
        sc.Eval ("var i, o = (" + .responsetext + ")")
        sc.Eval "function l(){return o.length;}"
        sc.Eval "function indx(n){i=n;}"
        sc.Eval "function p(pname){return o[i][pname];}"

        .abort

        With Sheets(1)
            For x = 1 To sc.Eval("l()")
                sc.Eval "indx(" & x - 1 & ")" 'set array index

                'get properties at that index
                .Cells(1, 2).Value = sc.Eval("p('name')")
                .Cells(1, 2).Value = sc.Eval("p('price_btc')")
                .Cells(1, 3).Value = sc.Eval("p('price_usd')")
                .Cells(1, 4).Value = sc.Eval("p('Rank')")

            Next x
        End With
    End With

End Sub

Object Vba read items

1 Answers1

Linked