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

查看：50
本文介绍了使用 VBA 和 VBA-JSON 从 Wordpress API 访问 JSON 数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

Sub Wordpress()'' WordPress API 测试'将 wpResp 调暗为变体Dim sourceSheet As StringDim resourceURL As StringsourceSheet = "资源"resourceURL = Sheets(sourceSheet).Cells(6, 1)wpResp = getJSON(resourceURL + "/wp-json/wp/v2/posts")结束子

以及它调用的函数.

函数 getJSON(link) As ObjectDim 响应作为字符串Dim json 作为对象出错时转到恢复Dim retryCount 作为整数重试计数 = 0Dim web As MSXML2.XMLHTTP60设置 web = 新 MSXML2.XMLHTTP60开始:web.OpenGET"，链接，假，用户名，密码web.setRequestHeader "Content-type", "application/json"网络发送响应 = web.responseText而 web.readyState <>4事件温德出错时转到 0调试.打印链接Debug.Print web.Status;"XMLHTTP 状态";web.statusText;"在 ";时间设置 json = JsonConverter.ParseJson(response)'getJSON = json ' 这一行产生 Object variable 或 With block variable not set 错误，但我可以稍后处理退出函数恢复:重试计数 = 重试计数 + 1Debug.Print "错误号:" &Err.Number &" " &错误描述&重试" &重试次数Application.StatusBar = "错误编号:" &Err.Number &" " &错误描述 &重试" &重试次数如果 retryCount <4 然后转到 the_start Else 退出函数结束函数

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

如果我使用即时窗口来查询 ?json.count 我得到正确的结果1"但是在网上研究了大约六个小时并尝试了我能找到的尽可能多的变体后，我仍然坚持如何访问其他 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>博客内容发布?有用的行业特定内容:1) 为读者提供有用的信息，2) 表明您是行业专家.<\/p>\n<p>使用您的公司&#8217;s 博客文章，对当前的行业主题发表意见，使您的公司人性化，并展示您的产品和服务如何帮助人们.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>博客文章有什么内容?有用的行业特定内容:1) 为读者提供有用的信息，2) 向您展示&#8217;re&hellip;<\/p>\n","protected":false},"作者":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，这里问题之外的进一步建议也将是有用的.

解决方案

PrintJSONAccessors:Sub

Sub PrintJSONAccessors(JSON 作为变体，可选前缀为字符串)Dim data As Variant, Key As Variant, Value As VariantDim Accessor As String, ArrayAccessor As String昏暗一样长如果 TypeName(JSON) = "Collection";然后对于 n = 1 到 JSON.Count访问器 = 前缀 &"("&n&")"如果 TypeName(JSON(n)) = "Dictionary";或 TypeName(JSON(n)) = "Collection";然后PrintJSONAccessors JSON(n), Accessor别的调试.打印访问器万一下一个别的对于 JSON 中的每个键如果 TypeName(Key) = "Dictionary"或 TypeName(Key) = "Collection";然后PrintJSONAccessors 键，前缀ElseIf TypeName(JSON(Key)) = "Dictionary";或 TypeName(JSON(Key)) = "Collection";然后访问器 = 前缀 &"("& Chr(34) & Key & Chr(34) & ")"PrintJSONAccessors JSON(Key), AccessorElseIf TypeName(JSON(Key)) = "Dictionary";然后访问器 = 前缀 &"("& Chr(34) & Key & Chr(34) & ")"PrintJSONAccessors JSON(Key), AccessorElseIf TypeName(JSON(Key)) = "Variant()";然后数据 = JSON(密钥)对于 n = LBound(data) 到 UBound(data)访问器 = 前缀 &"("& Chr(34) & Key & Chr(34) & ")"ArrayAccessor = 前缀 &"("& Chr(34) & Key & Chr(34) & ")"&"("&n&")"如果 TypeName(data(n)) = "Dictionary";然后PrintJSONAccessors data(n), ArrayAccessor别的Debug.Print ArrayAccessor万一下一个别的访问器 = 前缀 &"("& Chr(34) & Key & Chr(34) & ")"调试.打印访问器万一下一个万一结束子

用法:

<块引用>

 PrintJSONAccessors JSON，?JSON"；

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

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

And the function it calls.

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

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:

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.

Here is the 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}]}}]

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.

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

Immediate Window

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

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

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

Collection|Dictionary|Dictionary|Collection|Boolean Value

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.

PrintJSONAccessors:Sub

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

Usage:

 PrintJSONAccessors JSON, "?JSON"

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.

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屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆