Solved. The function to build the array could be more efficient/neater code but it seems to do the job.

Option Explicit
Option Private Module

'alter URL & workbook name to suit your needs
Private Const strURL As String = "http://www.rba.gov.au/statistics/hist-exchange-rates/2010-2013.xls"

#If VBA7 And Win64 Then
    Private Declare PtrSafe Function URLDownloadToFile _
        Lib "urlmon.dll" Alias "URLDownloadToFileA" ( _
            ByRef pCaller As LongPtr, _
            ByVal szURL As String, _
            ByVal szFileName As String, _
            ByVal dwReserve As Long, _
            ByRef lpfnCB As LongPtr) _
        As LongPtr
#Else
    Private Declare Function URLDownloadToFile _
        Lib "urlmon" Alias "URLDownloadToFileA" ( _
            ByVal pCaller As Long, _
            ByVal szURL As String, _
            ByVal szFileName As String, _
            ByVal dwReserved As Long, _
            ByVal lpfnCB As Long) _
        As Long
#End If

Public Function DownloadExcel_Read_Kill() As Variant
'call this function to obtain a customised array from a downloadable workbook
    Dim strDownloadFullPath     As String
    Dim wbExcelDownload         As Workbook
    Dim lngFileName             As Long

    Dim ext                     As String
    Dim buf                     As Variant
    Dim ret                     As Long

'   determine file extension
    buf = Split(strURL, ".")
    ext = "." & buf(UBound(buf))
    buf = vbNull

'   set download file path & name
    lngFileName = Now()
    strDownloadFullPath = Environ("Temp") & Application.PathSeparator & lngFileName & ext
    lngFileName = vbNull

'   download file
    ret = URLDownloadToFile(0, strURL, strDownloadFullPath, 0, 0)

'   check if file downloaded successfully
    If Not ret = 0 Then
        MsgBox "Download failed", vbCritical, "ERROR"
        Exit Function
    End If

'   file open & read
    Application.ScreenUpdating = False
    Set wbExcelDownload = Workbooks.Open(FileName:=strDownloadFullPath, ReadOnly:=True, AddtoMRU:=False)
    strDownloadFullPath = vbNullString

'   alter the array function to suit to your own needs
    DownloadExcel_Read_Kill = RbaArrayRange(wbExcelDownload)

'   file close & kill
    With wbExcelDownload
        .Saved = True
        On Error Resume Next
        .ChangeFileAccess Mode:=xlReadOnly
        On Error GoTo 0
        Kill .FullName
        .Close SaveChanges:=False
    End With

    Set wbExcelDownload = Nothing
    Application.ScreenUpdating = True
End Function

Private Function RbaArrayRange(wbWorkbook As Workbook) As Variant
    Dim lngBottom   As Long
    Dim lngTop      As Long

    With wbWorkbook
        With .ActiveSheet
            'get last row
            lngBottom = .Cells(Rows.Count, "A").End(xlUp).Row
            'get top row
            lngTop = lngBottom
            Do While .Cells(lngTop - 1, "A").Value = .Cells(lngTop, "A").Value - 1
                lngTop = lngTop - 1
            Loop
            'set array
            RbaArrayRange = Range(.Cells(lngTop, 1), .Cells(lngBottom, 8))
        End With
    End With
End Function