时间戳

首页 > 解决方案 > JSON 导入生成运行时错误“13”类型不匹配

arrays - JSON 导入生成运行时错误“13”类型不匹配

问题描述

尝试使用 JSON 通过 IEX API 导入某些数据时，我收到运行时错误“13”类型不匹配。

我在为 For Each 循环中的单元格设置值时收到错误消息。

这是查看 API 数据的链接： https ://api.iextrading.com/1.0/stock/aapl/financials?period=annual

Sub getFinancials()

'Write to ws
Dim ws As Worksheet
Set ws = Sheets("Financials")

Dim ticker As String
ticker = ws.Range("P7").value

Dim lastrow As Long
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

'Clear Range
ws.Range("A1:L" & lastrow).Clear

'Array Column Headers
Dim myarray As Variant
myarray = Array("reportDate", "grossProfit", "costOfRevenue", "operatingRevenue", "totalRevenue", "operatingIncome", "netIncome", "researchAndDevelopment", "operatingExpense", "currentAssets", "totalAssets", "totalLiabilities", "currentCash", "currentDebt", "totalCash", "totalDebt", "shareholderEquity", "cashChange", "cashFlow", "operatingGainsLosses")
Arrsize = UBound(myarray) - LBound(myarray) + 1

Dim rngTarget As Range
Set rngTarget = ws.Range(Cells(2, 1), Cells(Arrsize + 1, 1))
rngTarget.value = Application.Transpose(myarray)    

'Send web request for API Data
u = "https://api.iextrading.com/1.0/stock/" & ticker & "/financialsperiod=annual"
' https://api.iextrading.com/1.0/stock/aapl/financials?period=annual
Set myrequest = CreateObject("WinHttp.WinHttpRequest.5.1")
myrequest.Open "Get", u
myrequest.Send

'Parse JSON
Dim JSON As Object
Set JSON = JsonConverter.ParseJson(myrequest.ResponseText)

'Get # of Objects in Array
Dim arrayLen As Integer
arrayLen = JSON.Count

'Loop through Elements
Dim element As Variant
Dim x, y, r As Integer
r = 2
y = 2
x = 1

While x < arrayLen + 1
    For Each element In myarray
        ws.Cells(r, y).value = JSON(2)(element)
        y = y + 1
    Next element

    y = 2
    x = x + 1
    r = r + 1
Wend

End Sub

标签： arraysjsonexcelvbatype-mismatch

解决方案

我刚刚通过转换器运行 JSON，这是我得到的结构：

  • -字典（2项）
  • --收藏（4项）
  • ---字典（20项）

您需要相应地提取数据。集合可以通过一个简单的 for each 循环来循环。字典可以通过以下结构循环。

Option Explicit


Sub PrintFinancialReports()

    Dim apiURL As String
    apiURL = "https://api.iextrading.com/1.0/stock/aapl/financials?period=annual"

    Dim myrequest As WinHttpRequest
    Set myrequest = New WinHttpRequest

    myrequest.Open "Get", apiURL
    myrequest.Send

    Debug.Print myrequest.ResponseText ' print received JSON to check if it is valid

    Dim FinancialReportQuery As Dictionary
    Set FinancialReportQuery = JsonConverter.ParseJson(myrequest.ResponseText)

    Debug.Print FinancialReportQuery.Item("symbol")

    Dim Reports As Collection
    Set Reports = FinancialReportQuery.Item("financials")

    Dim report As Dictionary
    For Each report In Reports
        Dim reportContentKey As Variant '<-- variant is needed to loop a dictionary
        For Each reportContentKey In report
            Debug.Print reportContentKey, report.Item(reportContentKey)
        Next reportContentKey
    Next report

End Sub

希望这可以帮助

推荐阅读