Looking up Australian postal codes

If you live in Australia, you'll need to know the post code for your letters, bills and postcards. Here's some VBA code and a sample app that can help you look up post codes in Australia.

The GetAustralianPostCodeByLocation function

The following function will query the web API located at webservicex.net and write a XML file to the local temp folder. The XML file is then loaded into a MSXML Document Object and parsed for the result. The resulting array will always be N rows deep x 2 columns wide.

Note that the function checks if the XML file already exists (i.e. the same query has already been performed), and loads the XML file instead of running the query again. This is because it is faster to load the existing file than to perform the (relatively) slower web query. If the file already exists, let's take advantage of that fact and reuse the data (and avoid over-querying the web service which could get us banned).

Function GetAustralianPostCodeByLocation(locationName As String) As String()
' http://www.webservicex.net/WCF/ServiceDetails.aspx?SID=29

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 locationNode As Object
Dim locationSubNode As Object

Const XML_FILE_EXTENSION As String = ".xml"

  ' if XML file exists, don't requery website
  tempFile = environ("temp") & "\" & locationName & XML_FILE_EXTENSION

  If Len(Dir(tempFile)) = 0 Then

    Set xml = CreateObject("MSXML2.XMLHTTP.6.0")

    With xml
      .Open "GET", _
            "http://www.webservicex.net/AustralianPostCode.asmx/GetAustralianPostCodeByLocation?Location=" & locationName, False
      .Send
    End With

    result = xml.responsetext

    ' save result as temp XML document
    tempFile = CreateXMLFile(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

  numRows = tables.Length
  numCols = tables.Item(0).childNodes.Length

  ' resize array
  ReDim tempString(1 To numRows, 1 To numCols)

  For i = 1 To numRows
    Set locationNode = tables.Item(i - 1)

    For j = 1 To numCols
      Set locationSubNode = locationNode.childNodes.Item(j - 1)
      tempString(i, j) = locationSubNode.nodeTypedValue
    Next j

  Next i

  GetAustralianPostCodeByLocation = tempString

End Function

Secondary functions

The MSXML helper functions should be placed in a module in the same project.

Sample usage

The following sample routine will look up all the postal codes for cities in Australia containing the string "Perth". It then prints out the city name and post code in the Immediate Window. The sample code below will also print out the unique city names it finds. This is because the code finds all matching city names, so you can use it to find all cities matching the input string (in addition to looking up the post code). We add the city names to a Collection to remove duplicates and ensure a unique list. This is because the web API returns each name twice (for some unknown reason).

Sub TestGetAustralianPostCodeByLocation()

Dim postCodeInfo() As String
Dim locationName As String
Dim i As Long
Dim j As Long
Dim coll As Collection

  locationName = "Perth"

  postCodeInfo = GetAustralianPostCodeByLocation(locationName)

  ' list city names and postal code
  For i = 1 To UBound(postCodeInfo)
    For j = 1 To UBound(postCodeInfo, 2) Step 2
      Debug.Print postCodeInfo(i, j) & " - " & postCodeInfo(i, j + 1)
    Next j
  Next i

  ' also works as a matching city name finder
  Set coll = New Collection
  For i = 1 To UBound(postCodeInfo)
    On Error Resume Next
    coll.Add postCodeInfo(i, 1), CStr(postCodeInfo(i, 1))
  Next i

  For i = 1 To coll.Count
    Debug.Print coll(i)
  Next i

End Sub