Kyle Kyle - 3 months ago 9
JSON Question

Loop until website has no additional JSON arrays to parse

I am looking for a loop function/syntax that will allow my loop to cease once the website I am pulling JSON arrays from has no additional arrays left to parse (variable / unknowable number of arrays).

Thank you for the insight.

sheetCount = 1
i = 1
urlArray = Array("URL array list")

Dim MyRequest As Object
Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")

Dim MyUrls
MyUrls = urlArray

Dim k As Long
Dim Json As Object

For k = LBound(MyUrls) To UBound(MyUrls)
With MyRequest
.Open "GET", MyUrls(k)
Set Json = JsonConverter.ParseJson(.ResponseText)
Sheets("Sheet" & sheetCount).Cells(i, 1) = Json("cars")(i)("carType")
Sheets("Sheet" & sheetCount).Cells(i, 2) = Json("cars")(i)("fare")("carprice")
i = i + 1
End With
sheetCount = sheetCount + 1


You are missing the UBound function.

Other notes

  • No code without Option Explicit, period. No exceptions.
  • Make small functions that do one thing only.
  • Add references to the libraries you use instead of using CreateObject. It will make your life a lot easier because this way you get compile-time type checking and Intellisense.
  • It's safer to use the Exists() method to check if a dictionary key exists before you try to access it. Trying to access a non-existing key will throw a run-time error.
  • I'm silently assuming that you are using

This should be close enough:

Option Explicit

Function GetJson(ByVal url As String) As Dictionary
    With New WinHttpRequest  ' see
        .Open "GET", url
        Set GetJson = JsonConverter.ParseJson(.ResponseText)
    End With
End Function

Sub FillCarInfo(data As Dictionary, sheet As Worksheet)
    Dim i As Integer, car As Dictionary
    For i = 0 To UBound(data("cars")) - 1
        Set car = data("cars")(i)
        ' you probably should use If car.Exists("carType") Then
        sheet.Cells(i, 1) = car("carType")
        sheet.Cells(i, 1) = car("fare")("carprice")
    Next i
End Sub

Sub FillMultipleCarInfo(urls As Variant, book As Workbook)
    Dim i As Integer, data As Dictionary, sheet As Worksheet

    For i = 0 To UBound(urls) - 1
        Set data = GetJson(urls(i))
        Set sheet = book.Sheets(i + 1)
        FillCarInfo data, sheet
    Next i
End Sub


Dim myUrls As Variant
myUrls = Array("URL array list")

FillMultipleCarInfo myUrls, ActiveWorkbook