使用VBA和VBA-JSON从Wordpress API访问JSON数据 [英] Using VBA and VBA-JSON to access JSON data from Wordpress API

问题描述

我正在构建一个VBA应用程序，该应用程序使用从网络抓取的资源来创建和修改Wordpress网站页面. Wordpress API返回一个JSON文件，但是在VBA中不支持解析JSON，因此我从GitHub导入了VBA-JSON.这是子例程:

I'm building a VBA app that creates and modifies Wordpress website pages using resources scraped from the web. The Wordpress API returns a JSON file but there is no native support for parsing JSON in VBA so I imported VBA-JSON from GitHub. Here is the subroutine:

Sub Wordpress()

    '
    ' Wordpress API Test
    '
    Dim wpResp As Variant
    Dim sourceSheet As String
    Dim resourceURL As String
    sourceSheet = "Resources"
    resourceURL = Sheets(sourceSheet).Cells(6, 1)
    wpResp = getJSON(resourceURL + "/wp-json/wp/v2/posts")

End Sub

它调用的函数.

Function getJSON(link) As Object

    Dim response As String
    Dim json As Object
    On Error GoTo recovery
    Dim retryCount As Integer
    retryCount = 0
    Dim web As MSXML2.XMLHTTP60
    Set web = New MSXML2.XMLHTTP60

the_start:

    web.Open "GET", link, False, UserName, pw
    web.setRequestHeader "Content-type", "application/json"
    web.send
    response = web.responseText
    While web.readyState <> 4
        DoEvents
    Wend

    On Error GoTo 0

    Debug.Print link
    Debug.Print web.Status; "XMLHTTP status "; web.statusText; " at "; Time

    Set json = JsonConverter.ParseJson(response)

    'getJSON = json ' this line produces Object variable or With block variable not set error but I can deal with it later

    Exit Function

recovery:

    retryCount = retryCount + 1
    Debug.Print "Error number: " & Err.Number & " " & Err.Description & " Retry " & retryCount
    Application.StatusBar = "Error number: " & Err.Number & " " & Err.Description & " Retry " & retryCount
    If retryCount < 4 Then GoTo the_start Else Exit Function
End Function

此代码返回一个包含1个项目的对象/集合，其中包含一个包含24个项目的变量/对象/字典，但是我对如何访问这些项目一无所知.这是屏幕截图:

This code returns an Object/Collection with 1 item that contains a Variant/Object/Dictionary with 24 items but I'm lost on how to access these items. Here is a screenshot:

如果我使用即时窗口查询?json.count，我得到正确的结果"1"，但是经过大约六个小时的网络研究并尝试了尽可能多的变体之后，我仍然对如何访问其他24个.

If I use the immediate window to query ?json.count I get the correct result "1" but after about six hours of researching on the web and trying as many variants as I could find, I'm still stuck on how to access the other 24.

这是JSON:

[{"id":1,"date":"2018-06-22T18:13:00","date_gmt":"2018-06-22T22:13:00","guid":{"rendered":"http:\/\/mytestsite.org\/?p=1"},"modified":"2018-06-22T18:13:00","modified_gmt":"2018-06-22T22:13:00","slug":"hello-world","status":"publish","type":"post","link":"http:\/\/mytestsite.org\/hello-world\/","title":{"rendered":"Blog Post Title"},"content":{"rendered":"<p>What goes into a blog post? Helpful, industry-specific content that: 1) gives readers a useful takeaway, and 2) shows you&#8217;re an industry expert. <\/p>\n<p>Use your company&#8217;s blog posts to opine on current industry topics, humanize your company, and show how your products and services can help people.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>What goes into a blog post? Helpful, industry-specific content that: 1) gives readers a useful takeaway, and 2) shows you&#8217;re&hellip;<\/p>\n","protected":false},"author":1,"featured_media":212,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"http:\/\/mytestsite.org\/wp-json\/wp\/v2\/posts\/1"}],"collection":[{"href":"http:\/\/mytestsite.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/mytestsite.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/mytestsite.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/mytestsite.org\/wp-json\/wp\/v2\/comments?post=1"}],"version-history":[{"href":"http:\/\/mytestsite.org\/wp-json\/wp\/v2\/posts\/1\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/mytestsite.org\/wp-json\/wp\/v2\/media\/212"}],"wp:attachment":[{"href":"http:\/\/mytestsite.org\/wp-json\/wp\/v2\/media?parent=1"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/mytestsite.org\/wp-json\/wp\/v2\/categories?post=1"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/mytestsite.org\/wp-json\/wp\/v2\/tags?post=1"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}]

在一天结束时，我希望能够整理从几笔互联网资源中提取并整理的几百页WP内容，并使用此应用程序使它们保持最新状态.只要我们不超出VBA的范围，此处提出的其他建议也将很有用.

At the end of the day, I want to be able to spin up a few hundred pages of WP content extracted and collated from several internet sources and keep them up to date using this app. Further suggestions beyond the problem here would also be useful so long as we don't get outside of VBA.

推荐答案

The JsonConverter is returning a collection of VBA.Collections Scripting.Dictionaries, and Values. In order to understand the output you will have to test the TypeName of all the returned values.

真正的问题是如何导航json对象(或与此相关的任何未知对象)并访问其中的值.

The real question is "How to navigate through a json object (or any unknown object for that matter) and access the values within.

使用OP帖子中的Immediate Window和json对象，我将尝试描述思考过程(以必读书籍的形式:

Using the Immediate Window and the json object from the OP's post I will try to describe the thought process (in the style of the must read book: The Little Schemer)

' What is json?
?TypeName(JSON)
Collection

'json is a collection
'How big is JSON
?JSON.Count
 1 

'JSON is a collection of 1 Item
'What is Type that Item?
?TypeName(JSON(1))
Dictionary

'JSON(1) is a Dictionary
'What is the first key in the JSON(1) Dictionary?
?JSON(1).Keys()(0)
id

'The first key in the JSON(1) Dictionary is "id"
'What is the Type of the value of "id"?
?TypeName(JSON(1)("id"))
Double

'JSON(1)("id") is a number
'What is its value
?JSON(1)("id")
 1 

当然，考虑到此JSON对象中的嵌套量，此过程很繁琐.

Of course this process can get tedious consider the amount of nesting in this JSON Object.

JSON(1)("_ links")("curies")(1)("templated")

JSON(1)("_links")("curies")(1)("templated")

集合|字典|字典|集合|布尔值

Collection|Dictionary|Dictionary|Collection|Boolean Value

所以我想最好的办法是编写一个函数，该函数会将所有访问器打印到Immediate Window并从那里访问.

So I guess the best thing to do is write a function that will print all the accessor to the Immediate Window and go from there.

Sub PrintJSONAccessors(JSON As Variant, Optional Prefix As String)
    Dim data As Variant, Key As Variant, Value As Variant
    Dim Accessor As String, ArrayAccessor As String
    Dim n As Long
    If TypeName(JSON) = "Collection" Then
        For n = 1 To JSON.Count
            Accessor = Prefix & "(" & n & ")"
            If TypeName(JSON(n)) = "Dictionary" Or TypeName(JSON(n)) = "Collection" Then
                PrintJSONAccessors JSON(n), Accessor
            Else
                Debug.Print Accessor
            End If
        Next
    Else
        For Each Key In JSON
            If TypeName(Key) = "Dictionary" Or TypeName(Key) = "Collection" Then
                PrintJSONAccessors Key, Prefix
            ElseIf TypeName(JSON(Key)) = "Dictionary" Or TypeName(JSON(Key)) = "Collection" Then
                Accessor = Prefix & "(" & Chr(34) & Key & Chr(34) & ")"
                PrintJSONAccessors JSON(Key), Accessor
            ElseIf TypeName(JSON(Key)) = "Dictionary" Then
                Accessor = Prefix & "(" & Chr(34) & Key & Chr(34) & ")"
                PrintJSONAccessors JSON(Key), Accessor
            ElseIf TypeName(JSON(Key)) = "Variant()" Then
                data = JSON(Key)
                For n = LBound(data) To UBound(data)
                    Accessor = Prefix & "(" & Chr(34) & Key & Chr(34) & ")"
                    ArrayAccessor = Prefix & "(" & Chr(34) & Key & Chr(34) & ")" & "(" & n & ")"
                    If TypeName(data(n)) = "Dictionary" Then
                        PrintJSONAccessors data(n), ArrayAccessor
                    Else
                        Debug.Print ArrayAccessor
                    End If
                Next
            Else
                Accessor = Prefix & "(" & Chr(34) & Key & Chr(34) & ")"
                Debug.Print Accessor
            End If
        Next
    End If
End Sub

用法:

 PrintJSONAccessors JSON, "?JSON"

---

看来MSScriptControl.ScriptControl仅适用于32位系统.我想这就是SIM在他的评论中提到的.尽管我的回答是IMO正确的，但您应该忽略下一部分评论.

---

It appears that the MSScriptControl.ScriptControl only works on 32 bit systems. I guess that is what SIM was alluding to in his comments. Although, my answer is IMO correct, you should ignore the next section of comments.

仅供参考::我发布了一个将JSON解析为数组和字典的函数返回a的函数代码审查上的使用VBA集合和数组的JSON类对象.它不能替代 JsonConverter 或omegastripes的 JSON.Bas .它说明您可以将 JScript 代码添加到CreateObject("MSScriptControl.ScriptControl")并使用它来处理JSON.

FYI: I posted a function that parses the JSON into Arrays and Dictionaries Function to Return a JSON Like Objects Using VBA Collections and Arrays on Code Review. It is not a replacement for JsonConverter or omegastripes's JSON.Bas. It demonstrates that you can add JScript code to CreateObject("MSScriptControl.ScriptControl") and use it to process the JSON.

这篇关于使用VBA和VBA-JSON从Wordpress API访问JSON数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！