对象Vba读取项目 [英] Object Vba read items

问题描述

我不能读取电影对象，我不知道为什么？我试图在Excel中返回一个JSOn API到我的工作表，购买我无法读取电影对象。

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

我创建一个名为API的工作表来返回值，但是我不知道电影对象中如何发现值元素

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:

推荐答案

您的问题是，如果JavaScript对象的某些属性的名称与VBA关键字（或可能的其他保留术语）匹配，则无法直接在VBA中进行访问。 name是一个例子 - 您将看到无法写入 Movie.name ，而不会将其切换到 Movie.Name

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

这是一种使用javascript访问属性的方法，但是您可能会更喜欢使用像VBAjson这样的东西（ https://github.com/VBA-tools/VBA-JSON ），因为它提供了更好的多用途方法。 p>

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

这篇关于对象Vba读取项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！