VBA Api integration - Empty Return

[sherloguz]

Hey guys. 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")
Debug.Print locationName

Dim parkID As Integer
parkID = response("parkID")
Debug.Print parkID

Dim parkName As String
parkName = response("parkName")
Debug.Print parkName

Dim lat As Double
lat = response("lat")
Debug.Print lat

Dim lng As Double
lng = response("lng")
Debug.Print lng

Dim capacity As Integer
capacity = response("capacity")
Debug.Print capacity

Dim emptyCapacity As Integer
emptyCapacity = response("emptyCapacity")
Debug.Print emptyCapacity

Dim updateDate As Date
updateDate = response("updateDate")
Debug.Print updateDate

Dim workHours As String
workHours = response("workHours")
Debug.Print workHours

Dim parkType As String
parkType = response("parkType")
Debug.Print parkType

Dim freeTime As Integer
freeTime = response("freeTime")
Debug.Print freeTime

Dim monthlyFee As Double
monthlyFee = response("monthlyFee")
Debug.Print monthlyFee

Dim tariff As String
tariff = response("tariff")
Debug.Print tariff

Dim district As String
district = response("district")
Debug.Print district

Dim address As String
address = response("address")
Debug.Print address

Dim areaPolygon As String
areaPolygon = response("areaPolygon")
Debug.Print areaPolygon

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

'Debug.Print Join(parkArray, "parkID")
End Sub

For example when I watch line 50 it should return "Teşvikiye Cami 1" but it just returns empty and error occurs "Invalid procedure call or argument". Anyone can help me with that?

 

[Domenic]

Your HTTP request actually returns a JSON file that contains a Collection, not a Dictionary. And the Collection only contains a single item, that being a Dictionary. Therefore, try the following instead...

Dim response As VBA.Collection
Set response = JsonConverter.ParseJson(request.responseText)

Dim dicData As Scripting.Dictionary
Set dicData = response(1)

Dim locationName As String
locationName = dicData("locationName")
Debug.Print locationName

...and so on.

Hope this helps!

 

[Domenic]

This is the JSON returned by your request...

[{"locationName":"0022 Şişli Hüsrev Gerede Caddesi","parkID":45,"parkName":"Hüsrev Gerede Sokak 1","lat":"41.0484","lng":"28.995","capacity":50,"emptyCapacity":14,"updateDate":"02.02.2023 18:31:36","workHours":"08:00-19:00","parkType":"YOL ÜSTÜ","freeTime":15,"monthlyFee":800.0,"tariff":"0-1 Saat : 28,00;1-2 Saat : 35,00;2-4 Saat : 45,00;4-8 Saat : 50,00;Tam Gün : 70,00","district":"ŞİŞLİ","address":"MERKEZ MAHALLESİ HÜSREV GEREDE CADDESİ","areaPolygon":"MULTIPOLYGON (((28.99524262 41.04829226, 28.9949504 41.04844549, 28.99493489 41.0484283, 28.99522635 41.04827563, 28.99524262 41.04829226), (28.99516386 41.0482723, 28.99475977 41.04848704, 28.99474199 41.04847096, 28.99514814 41.04825296, 28.99516386 41.0482723), (28.99484213 41.04850311, 28.99472555 41.04856406, 28.99470853 41.04854743, 28.99482587 41.04848591, 28.99484213 41.04850311)))"}]

You'll notice that it starts with first a left square bracket, and then a left brace bracket.

The left square bracket indicates the start of a collection.

The left brace bracket indicates the start of a dictionary.

Hope this helps!

 

[sherloguz]

Thank you so much Domenic. It helps after I stated response as Collection.

 

[Domenic]

You're very welcome, I'm glad I could help.

Cheers!