VBA-Json解析嵌套JSON报错求助：对象不支持该属性或方法

Hey there! Let's fix that "object doesn't support this property or method" error you're hitting, and walk through a solid example of parsing nested JSON data in VBA. First, big thanks to @QHarr for collaborating on this with me!

Why You're Seeing That Error #

The error on your line For Each Amount_Product In Orders("amount_product") almost certainly comes from misreferencing the nested structure. If your Orders variable points to the top-level "orders" array in your JSON, you can't directly access "amount_product" from it—you need to first loop through each individual order object inside the orders array, then access the amount_product values from each order.

Working Example with VBA-JSON #

First, make sure you're using the VBA-JSON library (a reliable, widely used tool for parsing JSON in VBA). Once you have that set up, here's a complete example to extract all amount_product values:

Sub ExtractAmountProductValues()
    Dim rawJson As String
    Dim parsedJson As Object
    Dim ordersCollection As Variant
    Dim singleOrder As Variant
    Dim productAmount As Variant
    
    ' Replace this with your actual JSON string (or load it from a file/API)
    rawJson = "{""orders"": [{""amount_product"": [15, 25, 35]}, {""amount_product"": [40, 50]}, {""other_key"": ""value""}]}"
    
    ' Parse the JSON string into a manipulable object
    Set parsedJson = JsonConverter.ParseJson(rawJson)
    
    ' Get the "orders" array from the parsed JSON
    Set ordersCollection = parsedJson("orders")
    
    ' Loop through each order in the orders array
    For Each singleOrder In ordersCollection
        ' Check if the current order has an "amount_product" property to avoid errors
        If singleOrder.Exists("amount_product") Then
            ' Loop through each value in the amount_product array
            For Each productAmount In singleOrder("amount_product")
                ' Do whatever you need with the value here—print to Immediate Window, write to cells, etc.
                Debug.Print "Extracted amount: " & productAmount
            Next productAmount
        End If
    Next singleOrder
End Sub

Key Takeaways #

• Navigate nested structures step by step: Start at the top-level JSON object, drill down to the orders array, then each individual order, and finally the amount_product values.
• Add existence checks: Using If singleOrder.Exists("amount_product") Then prevents errors if some orders don't include this property.
• Verify variable types: Ensure ordersCollection is treated as a collection/array (not a dictionary) when using For Each loops.

This pattern works for any nested JSON structure—just adjust the property names ("orders", "amount_product") to match your actual data.

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