使用 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.这是子程序:
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>使用您的公司’s 博客文章,对当前的行业主题发表意见,使您的公司人性化,并展示您的产品和服务如何帮助人们.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>博客文章有什么内容?有用的行业特定内容:1) 为读者提供有用的信息,2) 向您展示’re…<\/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’re an industry expert. <\/p>\n<p>Use your company’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’re…<\/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屋!