Using our trusty old webservicex.net API we can grab information on any airport in any country. I've written a few functions to do so, for use in any VBA-enabled program. We'll see how we can look at all the airports in a given country, and select an individual airport. At the bottom of this article you'll also find a sample Excel app for browsing through airport info by country and airport.
1: GetAirportInformationByCountry
The following function will return a two-dimensional array of data about all the airports in a given country. See the comments inside the function for the position and content of each array element.
This code requires MSXML 6.0 which should be found in your local system32 folder as msxml6.dll.
Make sure your system meets the requirements for running the code found on this page.
Function GetAirportInformationByCountry(country As String) As String()
' http://www.webservicex.net/WCF/ServiceDetails.aspx?SID=36
' returns:
' GetAirportInformationByCountry(n,1) = Airport Code
' GetAirportInformationByCountry(n,2) = City or Airport Name
' GetAirportInformationByCountry(n,3) = Country
' GetAirportInformationByCountry(n,4) = Country Abbreviation
' GetAirportInformationByCountry(n,5) = Country Code
' GetAirportInformationByCountry(n,6) = GMT Offset
' GetAirportInformationByCountry(n,7) = Runway Length (ft.)
' GetAirportInformationByCountry(n,8) = Runway Elevation (ft.)
' GetAirportInformationByCountry(n,9) = Latitude (deg)
' GetAirportInformationByCountry(n,10) = Latitude (min)
' GetAirportInformationByCountry(n,11) = Latitude (sec)
' GetAirportInformationByCountry(n,12) = Latitude (N/S)
' GetAirportInformationByCountry(n,13) = Longitude (deg)
' GetAirportInformationByCountry(n,14) = Longitude (min)
' GetAirportInformationByCountry(n,15) = Longitude (sec)
' GetAirportInformationByCountry(n,16) = Longitude (E/W)
Dim xml As Object
Dim result As String
Dim tempFile As String
Dim xmlDoc As Object
Dim xmlDocRoot As Object
Dim newDataSet As Object
Dim tables As Object
Dim i As Long, j As Long
Dim numRows As Long
Dim numCols As Long
Dim tempString() As String
Dim airport As Object
Dim airportSubNode As Object
Const XML_FILE_EXTENSION As String = ".xml"
' if XML file exists, don't requery website
tempFile = environ("temp") & "\" & country & "Airports" & XML_FILE_EXTENSION
If Len(Dir(tempFile)) = 0 Then
Set xml = CreateObject("MSXML2.XMLHTTP.6.0")
With xml
.Open "GET", _
"http://www.webservicex.net/airport.asmx/GetAirportInformationByCountry?country=" _
& country, False
.Send
End With
result = xml.responsetext
' save result as temp XML document
tempFile = CreateFile(tempFile, result)
End If
' load XML file into new XML document
Set xmlDoc = CreateObject("MSXML2.DOMDocument")
With xmlDoc
.async = False
.validateOnParse = False
.Load tempFile
End With
' check that the XML doc loaded
If LoadError(xmlDoc) Then
Exit Function
End If
' get root node
Set xmlDocRoot = GetRootNode(xmlDoc)
' get first level child nodes
Set newDataSet = GetChildNodes(xmlDocRoot)
' get second level nodes
Set tables = newDataSet.Item(0).childNodes
' resize array
numRows = tables.Length
numCols = tables.Item(0).childNodes.Length
ReDim tempString(1 To numRows, 1 To numCols)
' get third level child nodes
For i = 1 To numRows
Set airport = tables.Item(i - 1)
For j = 1 To numCols
Set airportSubNode = airport.childNodes.Item(j - 1)
tempString(i, j) = airportSubNode.nodeTypedValue
Next j
Next i
GetAirportInformationByCountry = tempString
End Function
The above function performs a web query and writes the result to a XML file in the local temp folder. If the file already exists, it is reused to avoid querying the web API needlessly. The XML file is loaded into a MSXML Document Object and parsed for the airport data. If there are 80 airports, the array will be 80 rows x 16 columns.
Sample usage
In the following sample, all the airports in Spain are returned to a String array. The airport names are then printed to the Immediate Window. The For Loop has to be stepped through in even numbers because for some reason, the web API returns each name twice.
Sub TestGetAirportInfoByCountry()
Dim airportsInfo() As String
Dim countryName As String
Dim i As Long
countryName = "Spain"
airportsInfo = GetAirportInformationByCountry(countryName)
' return airport names
For i = 1 To UBound(airportsInfo) Step 2
Debug.Print airportsInfo(i, 2)
Next i
End Sub
2: GetAirportInformationByAirportCode
The following function does essentially the same thing as GetAirportInformationByCountry, with a couple of important differences. It first checks if the output of GetAirportInformationByCountry is found, if so then it parses that XML file, otherwise it checks if GetAirportInformationByCountry has been run for the same country and airport and parses that XML file. Only if both files are not found will the web query be run. This is so we can avoid the (relatively) slower web query if the data has already been downloaded (and the temp folder hasn't been emptied).
Function GetAirportInformationByAirportCode(country As String, airportCode As String) As String()
' http://www.webservicex.net/WCF/ServiceDetails.aspx?SID=36
' returns:
' GetAirportInformationByAirportCode(n,1) = Airport Code
' GetAirportInformationByAirportCode(n,2) = City or Airport Name
' GetAirportInformationByAirportCode(n,3) = Country
' GetAirportInformationByAirportCode(n,4) = Country Abbreviation
' GetAirportInformationByAirportCode(n,5) = Country Code
' GetAirportInformationByAirportCode(n,6) = GMT Offset
' GetAirportInformationByAirportCode(n,7) = Runway Length (ft.)
' GetAirportInformationByAirportCode(n,8) = Runway Elevation (ft.)
' GetAirportInformationByAirportCode(n,9) = Latitude (deg)
' GetAirportInformationByAirportCode(n,10) = Latitude (min)
' GetAirportInformationByAirportCode(n,11) = Latitude (sec)
' GetAirportInformationByAirportCode(n,12) = Latitude (N/S)
' GetAirportInformationByAirportCode(n,13) = Longitude (deg)
' GetAirportInformationByAirportCode(n,14) = Longitude (min)
' GetAirportInformationByAirportCode(n,15) = Longitude (sec)
' GetAirportInformationByAirportCode(n,16) = Longitude (E/W)
Dim xml As Object
Dim result As String
Dim tempFile As String
Dim xmlDoc As Object
Dim xmlDocRoot As Object
Dim newDataSet As Object
Dim tables As Object
Dim i As Long, j As Long
Dim numRows As Long
Dim numCols As Long
Dim tempString() As String
Dim airport As Object
Dim airportSubNode As Object
Dim fileExists As Boolean
Const XML_FILE_EXTENSION As String = ".xml"
' if country airports XML file exists, don't requery website
tempFile = environ("temp") & "\" & country & "Airports" & XML_FILE_EXTENSION
If Len(Dir(tempFile)) = 0 Then
' check if airport-specific XML file exists
tempFile = environ("temp") & "\" & country & airportCode & XML_FILE_EXTENSION
If Len(Dir(tempFile)) = 0 Then
Set xml = CreateObject("MSXML2.XMLHTTP.6.0")
With xml
.Open "GET", _
"http://www.webservicex.net/airport.asmx/getAirportInformationByAirportCode?airportCode=" & airportCode, False
.Send
End With
result = xml.responsetext
' save result as temp XML document
tempFile = CreateFile(tempFile, result)
End If
Else
fileExists = True
End If
' load XML file into new XML document
Set xmlDoc = CreateObject("MSXML2.DOMDocument")
With xmlDoc
.async = False
.validateOnParse = False
.Load tempFile
End With
' check that the XML doc loaded
If LoadError(xmlDoc) Then
Exit Function
End If
' get root node
Set xmlDocRoot = GetRootNode(xmlDoc)
' get first level child nodes
Set newDataSet = GetChildNodes(xmlDocRoot)
' get second level nodes
Set tables = newDataSet.Item(0).childNodes
' resize array
numCols = tables.Item(0).childNodes.Length
ReDim tempString(1 To 1, 1 To numCols)
' if country airports XML file exists, parse it
If fileExists Then
For i = 1 To tables.Length
Set airport = tables.Item(i - 1).childNodes
Set airportSubNode = airport.Item(0)
If airportSubNode.nodeTypedValue = airportCode Then
For j = 1 To numCols
tempString(1, j) = airport.Item(j - 1).nodeTypedValue
Next j
Exit For
End If
Next i
Else
' parse the airport-specific XML file
Set airport = tables.Item(0).childNodes
For j = 1 To numCols
tempString(j) = airport.Item(j - 1).nodeTypedValue
Next j
End If
GetAirportInformationByAirportCode = tempString
End Function
Sample usage
In the following sample, the airport information for airport code FUE in Spain is returned by the function. We then print the airport's elevation.
Sub TestGetAirportInfoByAirportCode() Dim airportinfo() As String Dim countryName As String Dim airportCode As String countryName = "Spain" airportCode = "FUE" airportinfo = GetAirportInformationByAirportCode(countryName, airportCode) ' print runway elevation Debug.Print airportinfo(1, 8) End Sub
Secondary functions
The GetAirportInformationByCountry and GetAirportInformationByAirportCode functions above require helper functions.
Sample workbook
Here's a sample implementation of the airport functions. We'll take a basic userform and add a few listboxes. To start the form, run the StartForm procedure located in the workbook (see below for links). First enter the name a country and click Search. You'll see the results of GetAirportInformationByCountry. Then from the list of airport codes, select one and in the bottommost listbox you'll see the result of GetAirportInformationByAirportCode. It's not a pretty form, it's just meant to demonstrate some of the above techniques.
Download the workbook — Excel 2003
Download the workbook — Excel 2007/2010
