时间戳

首页 > 解决方案 > VBA解析JSON空数据

json - VBA解析JSON空数据

问题描述

我正在尝试使用 VBA 将一些数据解析为工作表。当提供 JSON 中的所有数据时，我的代码工作正常，但是当没有 (team1)(name) 或 (team1)(id) 时，我收到数据不兼容的错误。

代码如下！它在错误之前写入了 3 或 4 行数据。

JSON数据在下面。

有没有办法避免没有所有数据的寄存器，或者当数据为空时只写一个“空”值？

Dim jsonText As String
Dim jsonObject As Object, item As Object
Dim i As Long
Dim ws As Worksheet

Set ws = Worksheets("Matchs")
jsonText = ws.Cells(1, 1)

Dim http As Object, JSON As Object

Set http = CreateObject("MSXML2.XMLHTTP")

http.Open "GET", "getMatches.json", False

http.Send

Set JSON = ParseJson(http.responseText)

i = 2

For Each item In JSON
    ws.Cells(i, 1) = item("id")
    ws.Cells(i, 2) = item("date")
    ws.Cells(i, 3) = item("title")
    ws.Cells(i, 5) = item("team1")("name")
    ws.Cells(i, 6) = item("team1")("id")

    i = i + 1
Next

JSON =>

[{"id":2342835,"date":1594731600000,
   "team1":{"name":"FATE","id":9863},
   "team2":{"name":"Budapest Five","id":9802},
   "format":"bo1",
   "event":{"name":"Eden Arena Malta Vibes Cup 3","id":5426},
   "stars":0,"live":false},
{"id":2342836,"date":1594731600000,
   "team1":{"name":"PACT","id":8248},
   "team2":{"name":"Singularity","id":6978},
   "format":"bo1",
    "event":{"name":"Eden Arena Malta Vibes Cup 3","id":5426},
    "stars":0,"live":false},
{"id":2342843,"date":1594735200000,
    "title":"Malta Vibes 3 - Group A Winners' Match",
    "stars":0,"live":false},
{"id":2342862,"date":1594735200000,
    "team1":{"name":"Nexus","id":7187},
    "team2":{"name":"BIG Academy","id":10254},"format":"bo3","event":{"name":"Betano Masters Europe 2020","id":5427},"stars":0,"live":false},{"id":2342834,"date":1594746000000,"team1":{"name":"sAw","id":10567},"team2":{"name":"Nexus","id":7187},"format":"bo3","event":{"name":"ESEA Advanced Season 34 Europe","id":5415},"stars":1,"live":false},{"id":2342844,"date":1594746000000,"title":"Malta Vibes 3 - Group A Elimination Match","stars":0,"live":false},{"id":2342863,"date":1594750500000,"team1":{"name":"Unicorns of Love","id":9812},"team2":{"name":"Giants","id":4949},"format":"bo3","event":{"name":"Betano Masters Europe 2020","id":5427},"stars":0,"live":false},{"id":2342801,"date":1594751400000,"team1":{"name":"Secret","id":10488},"team2":{"name":"Tricked","id":4602},"format":"bo3","event":{"name":"ESEA Advanced Season 34 Europe","id":5415},"stars":0,"live":false},{"id":2342845,"date":1594756800000,"title":"Malta Vibes 3 - Group A Decider Match","stars":0,"live":false},{"id":2342803,"date":1594774800000,"team1":{"name":"Thunder Logic","id":9615},"team2":{"name":"RBG","id":10258},"format":"bo3","event":{"name":"ESEA Advanced Season 34 North America","id":5416},"stars":0,"live":false},{"id":2342864,"date":1594776600000,"team1":{"name":"Third Impact","id":10469},"team2":{"name":"Lethal Divide","id":10770},"format":"bo3","event":{"name":"ESEA Advanced Season 34 North America","id":5416},"stars":0,"live":false},{"id":2342816,"date":1594796400000,"team1":{"name":"Hard Legion","id":10421},"team2":{"name":"AGF","id":8704},"format":"bo3","event":{"name":"Nine to Five 1","id":5409},"stars":0,"live":false},{"id":2342817,"date":1594796400000,"team1":{"name":"Gambit Youngsters","id":9976},"team2":{"name":"ALTERNATE aTTaX","id":4501},"format":"bo3","event":{"name":"Nine to Five 1","id":5409},"stars":0,"live":false},{"id":2342818,"date":1594807200000,"title":"Nine to Five 1 Grand Final","stars":0,"live":false},{"id":2342837,"date":1594818000000,"team1":{"name":"HellRaisers","id":5310},"team2":{"name":"HONORIS","id":10737},"format":"bo1","event":{"name":"Eden Arena Malta Vibes Cup 3","id":5426},"stars":0,"live":false},{"id":2342838,"date":1594818000000,"team1":{"name":"AGF","id":8704},"team2":{"name":"CR4ZY","id":10150},"format":"bo1","event":{"name":"Eden Arena Malta Vibes Cup 3","id":5426},"stars":0,"live":false},{"id":2342846,"date":1594821600000,"title":"Malta Vibes 3 - Group B Winners' Match","stars":0,"live":false},{"id":2342847,"date":1594832400000,"title":"Malta Vibes 3 - Group B Elimination Match","stars":0,"live":false},{"id":2342848,"date":1594843200000,"title":"Malta Vibes 3 - Group B Decider Match","stars":0,"live":false},{"id":2342532,"date":1594890000000,"team1":{"name":"Rooster","id":9881},"team2":{"name":"Paradox","id":7983},"format":"bo3","event":{"name":"LPL Pro League Season 5","id":5319},"stars":0,"live":false},{"id":2342839,"date":1594893600000,"team1":{"name":"Gambit Youngsters","id":9976},"team2":{"name":"Lyngby Vikings","id":8963},"format":"bo1","event":{"name":"Eden Arena Malta Vibes Cup 3","id":5426},"stars":0,"live":false},{"id":2342840,"date":1594897200000,"team1":{"name":"Illuminar","id":8813},"team2":{"name":"AVEZ","id":9797},"format":"bo1","event":{"name":"Eden Arena Malta Vibes Cup 3","id":5426},"stars":0,"live":false},{"id":2342800,"date":1594899000000,"team1":{"name":"Mako","id":10507},"team2":{"name":"TRUCKERS WITH ATTITUDE","id":10713},"format":"bo3","event":{"name":"LPL Pro League Season 5","id":5319},"stars":0,"live":false},{"id":2342849,"date":1594900800000,"title":"Malta Vibes 3 - Group C Winners' Match","stars":0,"live":false},{"id":2342850,"date":1594911600000,"title":"Malta Vibes 3 - Group C Elimination Match","stars":0,"live":false},{"id":2342851,"date":1594922400000,"title":"Malta Vibes 3 - Group C Decider Match","stars":0,"live":false},{"id":2342824,"date":1594962000000,"team1":{"name":"Invictus","id":7966},"team2":{"name":"D13","id":8607},"format":"bo3","event":{"name":"Perfect World Asia League Summer 2020","id":5376},"stars":0,"live":false},{"id":2342825,"date":1594971000000,"team1":{"name":"ViCi","id":7606},"team2":{"name":"Lucid Dream","id":8680},"format":"bo3","event":{"name":"Perfect World Asia League Summer 2020","id":5376},"stars":0,"live":false},{"id":2342826,"date":1594980000000,"team1":{"name":"TYLOO","id":4863},"team2":{"name":"Divine Vendetta","id":10396},"format":"bo3","event":{"name":"Perfect World Asia League Summer 2020","id":5376},"stars":0,"live":false},{"id":2342841,"date":1594980000000,"team1":{"name":"Hard Legion","id":10421},"team2":{"name":"SG.pro","id":10105},"format":"bo1","event":{"name":"Eden Arena Malta Vibes Cup 3","id":5426},"stars":0,"live":false},{"id":2342842,"date":1594983600000,"team1":{"name":"ALTERNATE aTTaX","id":4501},"team2":{"name":"Syman","id":8772},"format":"bo1","event":{"name":"Eden Arena Malta Vibes Cup 3","id":5426},"stars":0,"live":false},{"id":2342852,"date":1594987200000,"title":"Malta Vibes 3 - Group D Winners' Match","stars":0,"live":false},{"id":2342827,"date":1594989000000,"team1":{"name":"TIGER","id":10661},"team2":{"name":"Beyond","id":8262},"format":"bo3","event":{"name":"Perfect World Asia League Summer 2020","id":5376},"stars":0,"live":false},{"id":2342853,"date":1594998000000,"title":"Malta Vibes 3 - Group D Elimination Match","stars":0,"live":false},{"id":2342854,"date":1595008800000,"title":"Malta Vibes 3 - Group D Decider Match","stars":0,"live":false},{"id":2342855,"date":1595059200000,"title":"Malta Vibes 3 - Quarter-Final #1","stars":0,"live":false},{"id":2342828,"date":1595062800000,"title":"PAL Summer - Semi-final #1","stars":0,"live":false},{"id":2342856,"date":1595070000000,"title":"Malta Vibes 3 - Quarter-Final #2","stars":0,"live":false},{"id":2342829,"date":1595073600000,"title":"PAL Summer - Semi-final #2","stars":0,"live":false},{"id":2342857,"date":1595080800000,"title":"Malta Vibes 3 - Quarter-Final #3","stars":0,"live":false},{"id":2342858,"date":1595091600000,"title":"Malta Vibes 3 - Quarter-Final #4","stars":0,"live":false},{"id":2342830,"date":1595149200000,"title":"PAL Summer - 3rd Place Decider","stars":0,"live":false},{"id":2342859,"date":1595152800000,"title":"Malta Vibes 3 - Semi-Final #1","stars":0,"live":false},{"id":2342831,"date":1595160000000,"title":"PAL Summer - Grand Final","stars":0,"live":false},{"id":2342860,"date":1595163600000,"title":"Malta Vibes 3 - Semi-Final #2","stars":0,"live":false},{"id":2342861,"date":1595174400000,"title":"Malta Vibes 3 - Grand Final","stars":0,"live":false},{"id":2342643,"date":1597134600000,"team1":{"name":"Ground Zero","id":8536},"team2":{"name":"Paradox","id":7983},"format":"bo3","event":{"name":"ESL Australia & NZ Championship Season 11","id":5318},"stars":0,"live":false},{"id":2342520,"date":1597147200000,"team1":{"name":"Bantz","id":10712},"team2":{"name":"TRUCKERS WITH ATTITUDE","id":10713},"format":"bo3","event":{"name":"ESL Australia & NZ Championship Season 11","id":5318},"stars":0,"live":false}]

标签： jsonexcelvba

解决方案

您可以使用字典Exists方法：

If item.Exists("team1") Then 

   If item("team1").Exists("name") Then 
       'record the name
   End If

End If

推荐阅读