Excel VBA to connect to an API (2.0)

[marc j]

Hello all,

I had an issue trying to connect to my first API call from Excel and John_w really helped.
Thank you John.

I now have the need to connect to a free API travel website https://iatacodes.org/.
I am getting an error in parsing the xml. All help is appreciated.

Regards,
Marc

Sub get_iataorg()

Dim Req As New XMLHTTP60
Dim entry As String
Dim latvb As Double
Dim lonvb As Double
Dim myApi As String
Dim response As IXMLDOMNode
Dim i, j As Integer


Dim WS As Worksheet: Set WS = ActiveSheet


'sets lat and long for GET request
latvb = "45.47060013"
lonvb = "-73.74079895"


'creates entry
myApi = Range("myAPIkey").Value


entry = "https://iatacodes.org/api/v6/nearby.xml?api_key=" & myApi & "&lat=" & latvb & "&lng=" & lonvb & "&distance=100"


'copies entry
Worksheets("MENU").Select
Range("C18") = entry


'Request the GET entry
Req.Open "GET", entry, False
Req.send


'sets response to GET request
Dim Resp As New DOMDocument60
Resp.LoadXML Req.responseText


Cells(20, 3) = Req.responseText
MsgBox (Req.responseText)




'the error is in the for next loop
'i have been trying several syntax options -
'the xml is captured, but I can't seem to extract it
'the error is object variable not set or with block variable not set
j = 0
For Each response In Resp.getElementsByTagName("response")
    i = i + 1
        WS.Cells(i + 20, 1 + i).Value = response.SelectNodes("response")(j).Text
       ' WS.Cells(i + 20, 3).Value = response.SelectNodes("code")(j).Text
       ' WS.Cells(i + 20, 4).Value = response.SelectNodes("name")(j).Text
       ' WS.Cells(i + 20, 5).Value = response.SelectNodes("country_name")(j).Text


    j = j + 1
Next response
End Sub