VBA调用Stripe API提取Excel数据：无法获取嵌套JSON字段

问题分析 #

你遇到的报错核心原因有两点：一是Stripe返回的JSON中部分嵌套字段（如billing_details或其下的address）可能为null，直接访问会触发运行时错误；二是"billing_details.email"这种点语法并不适用于VBA的JsonConverter解析后的对象结构。

解决方案 #

JsonConverter解析JSON后，嵌套对象会被转换为Dictionary对象，需先判断字段是否存在且不为Null，再逐层访问嵌套字段。以下是修改后的完整代码：

Sub StripeTransactions()

    Dim objRequest As Object
    Dim strUrl As String
    Dim strApiKey As String
    Dim strResponse As String
    Dim jsonResponse As Object
    Dim i As Integer
    Dim ws As Worksheet
    Dim billingDetails As Object ' 存储billing_details对应的字典
    Dim addressDetails As Object ' 存储address对应的字典
    
    strApiKey = "sk_live_<redacted>"
    
    strUrl = "https://api.stripe.com/v1/charges?currency=eur&created[gte]=1667260799&created[lte]=1704067199" 
    
    Set objRequest = CreateObject("MSXML2.ServerXMLHTTP")
   
    With objRequest
        .Open "GET", strUrl, False
        .setRequestHeader "Authorization", "Bearer " & strApiKey
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" 
       
        .send
              
        strResponse = .responseText
    End With
    
    Set jsonResponse = JsonConverter.ParseJson(strResponse)
    
    Set ws = ThisWorkbook.Sheets.Add
    
    ' 扩展表头，包含嵌套字段
    ws.Cells(1, 1).Value = "Transaction ID"
    ws.Cells(1, 2).Value = "Amount (EUR)"
    ws.Cells(1, 3).Value = "Description"
    ws.Cells(1, 4).Value = "Date & Time"
    ws.Cells(1, 5).Value = "Status"
    ws.Cells(1, 6).Value = "Email"
    ws.Cells(1, 7).Value = "Name"
    ws.Cells(1, 8).Value = "City"
    ws.Cells(1, 9).Value = "Country"
    
    i = 2 
    For Each charge In jsonResponse("data")
        ' 提取一级字段
        ws.Cells(i, 1).Value = charge("id")
        ws.Cells(i, 2).Value = charge("amount") / 100 
        ws.Cells(i, 3).Value = charge("description")
        ws.Cells(i, 4).Value = DateAdd("s", charge("created"), "1/1/1970")
        ws.Cells(i, 5).Value = charge("status")
        
        ' 提取二级字段：billing_details
        If Not IsNull(charge("billing_details")) Then
            Set billingDetails = charge("billing_details")
            ws.Cells(i, 6).Value = billingDetails("email")
            ws.Cells(i, 7).Value = billingDetails("name")
            
            ' 提取三级字段：address
            If Not IsNull(billingDetails("address")) Then
                Set addressDetails = billingDetails("address")
                ws.Cells(i, 8).Value = addressDetails("city")
                ws.Cells(i, 9).Value = addressDetails("country")
            End If
        End If
        
        i = i + 1
    Next charge
    
    ' 释放对象资源
    Set objRequest = Nothing
    Set jsonResponse = Nothing
    Set billingDetails = Nothing
    Set addressDetails = Nothing
    Set ws = Nothing

End Sub

关键说明 #

1. 空值校验：每次访问嵌套字段前用IsNull()判断是否为空，避免因字段缺失或为null导致的报错。
2. 逐层访问：将嵌套字典赋值给变量后再提取字段，这是JsonConverter解析后访问嵌套结构的标准方式。
3. 语法规范：VBA的JsonConverter不支持charge("billing_details.email")这种点语法，必须通过多层字典索引（charge("billing_details")("email")）访问，但需先确保父字段不为空。

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