Excel VBA to connect to API

[marc j]

Excel VBA to connect to API


Hello all,
I am trying to use Excel to do an API request.
I was able to recreate the code from a popular youtube video, "Excel VBA Using A Web Service with XMLHTTP - Weather Forecast".
I am trying to access https://flightaware.com/commercial/flightxml but I can't seem to get the code.


I am new to VBA. All help is appreciated!


Regards,
Marc

 Option Explicit


Sub GetFlighInfo()




Dim W As Worksheet: Set W = ActiveSheet


Dim XMLReq As New MSXML2.XMLHTTP60
Dim HTMLDoc As New MSHTML.HTMLDocument
Dim myInfo As String




'assigns my info
myInfo = "http://myApi@flightxml.flightaware.com/json/FlightXML2/"


'-----------
'how do I request dal82 flight status (Delta Airlines flight 82)
'-----------


'request
XMLReq.Open "GET", myInfo, False
XMLReq.send




If XMLReq.Status <> 200 Then
    MsgBox "ERROR"
End If


'response
HTMLDoc.body.innerHTML = XMLReq.responseText
W.Cells(5, 5).Value = HTMLDoc.body.innerHTML
 
End Sub

 

[John_w]

I actually registered an account on FlightAware.com to see how to interact with the API, however in order to get an API key they want my credit card to bill me for API requests, so I stopped and didn't take it any further.

I have written the following code based on the documentation, Documentation ✈ Flight Status API / Flight Tracking API / FlightAware API ✈ Commercial Services ✈ FlightAware and the specific FlightInfo URL request - FlightXML2 Explorer. However, without the API key I'm unable to test this code.

You will also need to parse the response (the responseText string). I think the response is in JSON format and there are many VBA JSON parsers available. The best and simplest parser I've found recently is https://stackoverflow.com/a/38304247, which uses Microsoft Script Control and VBA's CallByName function. That parser deserves a much higher rating, however the presentation of the answer and the code is somewhat lacking.

Option Explicit

Public Sub Get_Flight_Info()

    Dim username As String, APIkey As String
    Dim URL As String
    Dim httpReq As Object 'MSXML2.XMLHTTP60
    
    username = "your_username"
    APIkey = "your_APIkey"
    
    'http://flightaware.com/commercial/flightxml/documentation2.rvt
    
    'To access any method, simply perform either a GET or POST request to http://flightxml.flightaware.com/json/FlightXML2/METHODNAME
    'using standard CGI-style representation of the arguments. All requests made must supply the username and API Key as a "basic" Authorization HTTP header.

    'http://flightaware.com/commercial/flightxml/explorer/#op_FlightInfo
    'ident   string  requested tail number, or airline with flight number
    'howMany int     maximum number of past flights to obtain. Must be a positive integer value less than or equal to 15, unless SetMaximumResultSize has been called.

    URL = "http://flightxml.flightaware.com/json/FlightXML2/FlightInfo?ident=dal82&howMany=1"
    
    Set httpReq = CreateObject("MSXML2.XMLHTTP.6.0")
    With httpReq
        .Open "GET", URL, False
        .setRequestHeader "Authorization", "Basic " & EncodeBase64(username & ":" & APIkey)
        .send
        MsgBox "Status = " & .statustext & vbNewLine & _
               "Response = " & .responseText
    End With

End Sub


Private Function EncodeBase64(plainText As String) As String

    Dim bytes() As Byte
    Dim objXML As Object 'MSXML2.DOMDocument60
    Dim objNode As Object 'MSXML2.IXMLDOMNode
    
    bytes = StrConv(plainText, vbFromUnicode)
   
    Set objXML = CreateObject("MSXML2.DOMDocument.6.0")
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = bytes
    EncodeBase64 = objNode.Text
    
    Set objNode = Nothing
    Set objXML = Nothing
    
End Function

Since I'm unable to test the above code, I can't really help you any further.

 

[marc j]

Thank you John, works like a charm!
Here is the response text:

{"FlightInfoResult":{"next_offset":1,"flights":[{"ident":"DAL82","aircrafttype":"A333","filed_ete":"08:25:00","filed_time":1497763587,"filed_departuretime":1497986700,"filed_airspeed_kts":454,"filed_airspeed_mach":"","filed_altitude":0,"route":"","actualdeparturetime":0,"estimatedarrivaltime":1498017600,"actualarrivaltime":0,"diverted":"","origin":"KATL","destination":"LFPG","originName":"Hartsfield-Jackson Intl","originCity":"Atlanta, GA","destinationName":"Charles de Gaulle/Roissy","destinationCity":"Paris"}]}}

This is perfect. This means that we can accomplish the same result as Javascript, Python, Ruby etc...

Thank you again!

Marc