VBA Json Parse-解析器没有正确分解Json

问题描述：

我一直试图从一个API解析一些JSON数据作为更大的项目的一部分，并且由于我对VBA/Json的经验有限，我很难计算看看我的VBA代码出现了什么问题。VBA Json Parse-解析器没有正确分解Json

我已经使用了下面的VBA Json解析器，并且基本都停止了在同一点工作，并给我一个“不完整的解析”。 VBA的JSON https://code.google.com/archive/p/vba-json vbjson www.ediy.co.nz/vbjson-json-parser-library-in-vb6-xidc55680

JSON的，我试图解析如下：

{"links":[],"content":{"carrier":{"allowedToOperate":"Y","bipdInsuranceOnFile":"1000","bipdInsuranceRequired":"Y","bipdRequiredAmount":"750","bondInsuranceOnFile":"0","bondInsuranceRequired":"N","brokerAuthorityStatus":"N","cargoInsuranceOnFile":"0","cargoInsuranceRequired":"N","carrierOperation":{"carrierOperationCode":"A","carrierOperationDesc":"Interstate"},"commonAuthorityStatus":"A","contractAuthorityStatus":"A","crashTotal":19,"dbaName":null,"dotNumber":124871,"driverInsp":974,"driverOosInsp":33,"driverOosRate":3.38809034907597535934291581108829568789,"driverOosRateNationalAverage":"5.51","ein":460396676,"fatalCrash":0,"hazmatInsp":0,"hazmatOosInsp":0,"hazmatOosRate":0,"hazmatOosRateNationalAverage":"4.5","injCrash":6,"isPassengerCarrier":"N","issScore":null,"legalName":"A&A EXPRESS LLC","oosDate":null,"oosRateNationalAverageYear":"2009-2010","phone":"6055822402","phyCity":"BRANDON","phyCountry":"US","phyState":"SD","phyStreet":"1015 9TH AVENUE NORTH","phyZipcode":"57005","reviewDate":"2016-09-23","reviewType":"N","safetyRating":"S","safetyRatingDate":"2008-08-29","safetyReviewDate":"2016-09-23","safetyReviewType":"N","snapshotDate":"1485493200000","statusCode":"A","totalDrivers":237,"totalPowerUnits":253,"towawayCrash":13,"vehicleInsp":477,"vehicleOosInsp":58,"vehicleOosRate":12.15932914046121593291404612159329140461,"vehicleOosRateNationalAverage":"20.72"},"links"

我的VBA代码如下所示：

Public Sub PARSEJSON() 
Dim reader As New XMLHTTP60 
Dim api As Object 
Dim WS As Worksheet 
Dim item As Variant 

reader.Open "GET", "https://mobile.fmcsa.dot.gov/qc/services/carriers/124871?webKey=APIKEY", False 
reader.send 

    Set WS = ActiveSheet 
    Set api = JSON.parse(reader.responseText) 
     For Each item In api 
      WS.Range("a3").Value = item("") 


     Next 


End Sub

我不能进入的JSON引用我需要的值，因为解析器产生类似的结果：

photo of watcher in VBA

它看起来像Json解析器只是从API中分解出Json字符串的一部分，它来自政府网站，所以我认为它应该采用通用的Json格式。我希望我在这篇文章中包含了足够的细节以涵盖我的问题的范围

请检查您的JSON字符串是有效的。 – PMerlet

我在https://jsonlint.com/检查了字符串，它说它有效的Json！ @Cubi – hankmoodie

答

我解决了这个问题，我进一步研究了如何引用字典，并意识到我使用了错误的语法来引用和添加额外的步骤不需要。

新VBA代码产生的结果我想：

Public Sub PARSEJSON() 
Dim reader As New XMLHTTP60 
Dim dict As Object 
Dim api As New Scripting.Dictionary 
Dim json As New Class1 
Dim WS As Worksheet 
Dim key As Variant 
Dim item As Variant 



reader.Open "GET", "https://mobile.fmcsa.dot.gov/qc/services/carriers/124871?webKey=APIKEY", False 
reader.send 

    Set WS = ActiveSheet 
    Set api = json.parse(reader.responseText) 
     MsgBox api("content")("carrier").Count 
     MsgBox api.item("content")("carrier")("driverOosInsp") 
     WS.Range("a1").value = api.item("content")("carrier")("driverOosInsp") 
End Sub

VBA Json Parse-解析器没有正确分解Json

相关推荐