如何使用VBA解析多层JSON？附具体JSON字符串解析故障求助

Hey there, let's fix this JSON parsing issue together! The core problem in your code is that when you loop through the territory Dictionary, Block_id is grabbing the key names (like "RSC", "HSC") instead of the actual sub-Dictionary objects that contain fields like sector, size, etc. That's why your inner loop wasn't pulling the values you wanted—it was actually iterating over the characters in the key string instead of the JSON fields.

Here's a revised, complete code that will parse all your target fields, including the nested racket structure:

Sub ParseTerritoryJSON()
    Dim response As Object
    Dim territory As Dictionary
    Dim blockKey As Variant
    Dim blockData As Dictionary
    Dim racketData As Dictionary
    Dim rowOffset As Integer
    
    ' Parse the JSON response into an object
    Set response = JsonConverter.ParseJson(request.responseText)
    Set territory = response("territory")
    
    rowOffset = 1 ' Start writing below A115 (matches your original offset logic)
    
    ' Loop through each territory block (RSC, HSC, etc.)
    For Each blockKey In territory
        ' Get the actual data dictionary for the current block
        Set blockData = territory(blockKey)
        
        ' Write block ID to column A
        Sheet7.Range("A115").Offset(rowOffset, 0).Value = blockKey
        
        ' Parse top-level fields into adjacent columns
        Sheet7.Range("A115").Offset(rowOffset, 1).Value = blockData("sector")
        Sheet7.Range("A115").Offset(rowOffset, 2).Value = blockData("size")
        Sheet7.Range("A115").Offset(rowOffset, 3).Value = blockData("density")
        Sheet7.Range("A115").Offset(rowOffset, 4).Value = blockData("slots")
        Sheet7.Range("A115").Offset(rowOffset, 5).Value = blockData("daily_respect")
        Sheet7.Range("A115").Offset(rowOffset, 6).Value = blockData("faction")
        Sheet7.Range("A115").Offset(rowOffset, 7).Value = blockData("coordinate_x")
        Sheet7.Range("A115").Offset(rowOffset, 8).Value = blockData("coordinate_y")
        
        ' Check if the block has a nested "racket" structure, then parse it
        If blockData.Exists("racket") Then
            Set racketData = blockData("racket")
            Sheet7.Range("A115").Offset(rowOffset, 9).Value = racketData("name")
            Sheet7.Range("A115").Offset(rowOffset, 10).Value = racketData("level")
            Sheet7.Range("A115").Offset(rowOffset, 11).Value = racketData("reward")
            ' Convert Unix timestamps to readable dates (adjust format as needed)
            Sheet7.Range("A115").Offset(rowOffset, 12).Value = DateAdd("s", racketData("created"), #1/1/1970#)
            Sheet7.Range("A115").Offset(rowOffset, 13).Value = DateAdd("s", racketData("changed"), #1/1/1970#)
        End If
        
        rowOffset = rowOffset + 1
    Next blockKey
    
    ' Clean up objects to avoid memory leaks
    Set racketData = Nothing
    Set blockData = Nothing
    Set territory = Nothing
    Set response = Nothing
End Sub

Key Fixes & Explanations: #

• Set blockData = territory(blockKey): This line grabs the actual Dictionary object tied to each block key (like "RSC"), which contains all the fields you need to parse.
• blockData.Exists("racket"): We check for the nested racket structure first to avoid runtime errors if a block doesn't have this field.
• Unix timestamp conversion: The created and changed values are converted from Unix timestamps to human-readable dates using DateAdd.

Quick Notes: #

• Make sure you have the VBA-JSON library properly imported (the source of JsonConverter). If you haven't already, add it via the VBA Editor's Tools > References menu.
• You can add header labels to row 115 (e.g., "Block ID", "Sector", "Size") to make your output table easier to read.

内容的提问来源于stack exchange，提问作者Andre Gouws