Importing Web data into Microsoft Excel

Why is it possible to import a table from a web page into Excel but not possible to import data from a downloadable (Excel) file into Excel?

Originally Posted by Kyle123

Because a table from a website is text so is directly parse-able, and Excel workbook is a binary object that isn't.

Ah, that explains why then.

From a practical POV, I will need to manually download the Excel file from the website and then direct the VBA code to the downloaded file to bring it in, right?

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

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