VBA Api integration - Empty Return

Question

I have a question. I have a site and I want to pull data through this website. I wrote a VBA code but it seems it always return empty even though there are data. Here is the code;

Sub dataxx()
Dim request As New WinHttpRequest
request.Open "Get", "https://api.ibb.gov.tr/ispark/ParkDetay?id=45"
request.Send

If request.Status <> 200 Then
    MsgBox request.ResponseText
    Exit Sub
End If

Dim response As Variant
Set response = JsonConverter.ParseJSON(request.ResponseText)
Dim parkArray() As String
ReDim parkArray(0 To 0)
On Error Resume Next
Dim locationName As String
locationName = response("locationName")

Dim parkID As Integer
parkID = response("parkID")

Dim parkName As String
parkName = response("parkName")

Dim lat As Double
lat = response("lat")

Dim lng As Double
lng = response("lng")

Dim capacity As Integer
capacity = response("capacity")

Dim emptyCapacity As Integer
emptyCapacity = response("emptyCapacity")

Dim updateDate As Date
updateDate = response("updateDate")

Dim workHours As String
workHours = response("workHours")

Dim parkType As String
parkType = response("parkType")

Dim freeTime As Integer
freeTime = response("freeTime")

Dim monthlyFee As Double
monthlyFee = response("monthlyFee")

Dim tariff As String
tariff = response("tariff")

Dim district As String
district = response("district")

Dim address As String
address = response("address")

Dim areaPolygon As String
areaPolygon = response("areaPolygon")

parkArray(15) = Array(locationName, parkID, parkName, lat, lng, capacity, emptyCapacity, updateDate, workHours, parkType, freeTime, monthlyFee, tariff, district, address, areaPolygon)

End Sub

What do you guys think of it? What is wrong with the code. If i didn't put "On Error Resume Next" then i get an error "Invalid procedure call or argument". Can anyone help me please with it?

I forgot to put this one before end sub "Debug.Print parkArray" . And when I add this to line type mismatch error occurs. — Oguzhan, Feb 02 '23 at 12:10
You can't `Debug.Print` an array like that. If it's a one-dimensional array, you could convert it into a string by `Join` ing the elements e.g. `Debug.Print Join(ArrayName, ",")` or you need to view the array in the Watches window. — CLR, Feb 02 '23 at 12:14
I get it now. But when I watch the lines it won't pull data. It just shows empty for all of it. For example I watch line 50 and waiting for response but it returns empty as it should return "Teşvikiye Cami 1". — Oguzhan, Feb 02 '23 at 12:20
You should almost NEVER use `On Error Resume Next`. https://stackoverflow.com/questions/31753201/vba-how-long-does-on-error-resume-next-work/31753321#31753321 — niton, Feb 03 '23 at 17:33

score 0 · Answer 1 · answered Feb 02 '23 at 13:58

The json is an array of records so you have to use response(1)("parkName")

[
    {
    "parkName": "H\u00fcsrev Gerede Sokak 1",
    "parkType": "YOL \u00dcST\u00dc",
    "updateDate": "02.02.2023 16:15:18",
    "workHours": "08:00-19:00"
    }
]

Option Explicit

Sub dataxx()

    Dim request As New WinHttpRequest, response As Variant
    Dim field, parkArray(), i As Long, msg As String

    request.Open "Get", "https://api.ibb.gov.tr/ispark/ParkDetay?id=45"
    request.Send
    
    If request.Status <> 200 Then
        MsgBox request.ResponseText, vbExclamation
        Exit Sub
    End If
    
    field = Array("locationName", "parkID", "parkName", "lat", "lng", _
    "capacity", "emptyCapacity", "updateDate", "workHours", "parkType", _
    "freeTime", "monthlyFee", "tariff", "district", "address", "areaPolygon")
    
    ReDim parkArray(0 To UBound(field))
    Set response = JsonConverter.ParseJson(request.ResponseText)
    
    With response(1)
        For i = 0 To UBound(parkArray)
            parkArray(i) = .Item(field(i))
            msg = msg & vbLf & field(i) & "=" & parkArray(i)
        Next
    End With
    MsgBox msg, vbInformation
   
End Sub

VBA Api integration - Empty Return

1 Answers1