Thanks Kyle. I'll give it a go


Here's where I have reached so far...

Option Explicit

Private Const strURL = "http://www.rba.gov.au/statistics/hist-exchange-rates/2010-2013.xls"

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

Public Sub DownloadExcel_Read_Kill()
    Dim strDownloadFullPath As String
    Dim wbExcelDownload As Workbook
    Dim ext As String
    Dim buf As Variant
    Dim ret As Long
    Dim lngFileName As Long

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

'   set download file path & name
    lngFileName = Now()
    strDownloadFullPath = Environ("Temp") & "\" & lngFileName & ext

'   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 Sub
    End If

'   file open & read
    Application.ScreenUpdating = False
    Set wbExcelDownload = Workbooks.Open(strDownloadFullPath)

    Debug.Print "To insert read code here"

'   file close & kill
    With wbExcelDownload
        .Saved = True
        .ChangeFileAccess Mode:=xlReadOnly
        Kill .FullName
        .Close SaveChanges:=False
    End With

    Application.ScreenUpdating = True
End Sub