使用VBA解析JSON时遇到问题 [英] Trouble parsing JSON with vba

问题描述

我可以从网络查询中获得出现的内容，以作为有效的JSON字符串，但是，我无法终生正确设置项目.需要确认我没有失去主意...

I can get what appears to be a valid JSON string from a web query, however, I cannot set items correctly for the life of me. Need to confirm that I'm not losing my mind...

'Call for available reports

Dim URLReporta As String
Dim JSONa As Object
Dim var As Object  
Set myrequesta = CreateObject("winhttp.winhttprequest.5.1")
URLReporta = ("https://secure.saashr.com:443/ta/rest/v1/reports?type=Saved&company%3shortname=" & Company)
myrequesta.Open "GET", URLReporta, False
myrequesta.setRequestHeader "Accept", "application/json"
myrequesta.setRequestHeader "Authentication", "Bearer " & Token
myrequesta.setRequestHeader "Content-Type", "application/json"
myrequesta.Send
Set JSONa = JsonConverter.ParseJson(myrequesta.responseText)
Set var = JSONa("SavedName")
Debug.Print var.Count

在Set var = JSONa("SavedName")行上出现错误:

运行时错误"424":需要对象

run-time error '424': object required

myrequesta.responseText值如下:

{报告":[{"SavedName":今年"，"SettingId":18959322}，{"SavedName":"Time Off Requests"，"SettingId":18960210}，{"SavedName":" Calc Hours Summary"，"SettingId":18960209}，{"SavedName":"roster"，"SettingId":18960211}，{"SavedName":"E/D/T"，"SettingId":18823042}，{"SavedName:" TestZDR，" SettingId:18957188​​}]}

{"reports":[{"SavedName":"This Year","SettingId":18959322},{"SavedName":"Time Off Requests","SettingId":18960210},{"SavedName":"Calc Hours Summary","SettingId":18960209},{"SavedName":"roster","SettingId":18960211},{"SavedName":"E/D/T","SettingId":18823042},{"SavedName":"TestZDR","SettingId":18957188}]}

推荐答案

JsonConverter.ParseJson函数返回的结构无法正常工作.对于您的特定JSON，它包含3个级别: 根级对象仅具有一个属性reports，该属性包含第二级数组，而第二级数组又包含6个第三级对象，它们具有属性SavedName和SettingId.您正在尝试从根级对象获取三级对象的属性值.

The structure returned by JsonConverter.ParseJson function doesn't work such way. For your particular JSON it contains 3 levels: Root-level object has only one property reports, which contains second-level array, which in turn contains 6 third-level objects, having properties SavedName and SettingId. You are trying to get third-level's object property value from root-level object.

首先，您需要获取第二级数组，然后遍历包含对象的元素，然后检索该对象的SavedName属性值.这是示例:

First you need to get second-level array, then loop through it's elements, containing objects, and retrieve the SavedName properties' values of that objects. Here is the example:

'Call for available reports

Dim URLReporta As String
Dim JSONa As Object
Dim var As Object
Dim rep As Variant
Set myrequesta = CreateObject("winhttp.winhttprequest.5.1")
URLReporta = ("https://secure.saashr.com:443/ta/rest/v1/reports?type=Saved&company%3shortname=" & Company)
myrequesta.Open "GET", URLReporta, False
myrequesta.setRequestHeader "Accept", "application/json"
myrequesta.setRequestHeader "Authentication", "Bearer " & Token
myrequesta.setRequestHeader "Content-Type", "application/json"
myrequesta.Send
Set JSONa = JsonConverter.ParseJson(myrequesta.responseText) ' root level object
Set var = JSONa("reports") ' second level array
For Each rep In var ' third level objects
    Debug.Print rep("SavedName") ' property "SavedName" value of current third level object
Next

以下是输出:

如果只想获取报告数，则获取数组和数组中的元素数:

If you want just to get the number of reports, then get the array and the number of elements in it:

Debug.Print JSONa("reports").Count

这篇关于使用VBA解析JSON时遇到问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！