Friday 19 January 2018

VBA - XHR - ADODB.Stream - Save a file from the Internet

Another code sample this time to download a file from the Internet using XmlHttp request (XHR) in combination with ADODB.Stream from binary writing to disk.


Option Explicit

'* Tools->References
'MSXML2             Microsoft XML, v6.0             C:\Windows\SysWOW64\msxml6.dll

Private Sub TestSaveFileFromInternet()
    Dim sListOfBanks As String
    sListOfBanks = "https://www.bankofengland.co.uk/-/media/boe/files/prudential-regulation/authorisations/" & _
                    "which-firms-does-the-pra-regulate/list-of-banks-november-2017-excel.xls/"

    SaveFileFromInternet sListOfBanks, "n:\list-of-banks-november-2017-excel.xls"

End Sub

Private Function SaveFileFromInternet(ByVal sUrl As String, ByVal sSaveToPath As String)
    
    Dim oXHR As MSXML2.XMLHTTP60
    Set oXHR = New MSXML2.XMLHTTP60

    oXHR.Open "GET", sUrl, False
    oXHR.send

    With CreateObject("ADODB.Stream")
        .Open
        .Type = 1
        .write oXHR.responseBody
        .SaveToFile sSaveToPath
        .Close
    End With

End Function

No comments:

Post a Comment