VBA Code to Download a File From the Website

Question

I am trying to download a file (historical Prime Interest rate) from the internet.

I am using the following code:

Sub DownloadHistoricalPrimeRates()

    Dim URL As String
    Dim FilePath As String
    Dim HTTP As Object
    Dim Stream As Object

    ' URL of the file to download
    URL = "blob:https://wowa.ca/c9c80084-6aa5-447a-8c54-3b1c58ab67f3"

    ' Path where the file will be saved (include file name and extension)
    FilePath = "\\Mac\iCloud\Differentiated Solutions\Model\Historical CORRA, DSIR and Prime\Preime-rate-History-wowa.csv"

    ' Create XMLHTTP object for file download
    Set HTTP = CreateObject("MSXML2.XMLHTTP")

    ' Create ADODB Stream object to write the file
    Set Stream = CreateObject("ADODB.Stream")

    ' Initialize request to download the file
    HTTP.Open "GET", URL, False
    HTTP.Send

    ' Check if the request was successful (status code 200)
    If HTTP.Status = 200 Then

        ' Open the stream and set properties
        Stream.Open
        Stream.Type = 1 ' Binary
        Stream.Write HTTP.responseBody

        ' Save the downloaded file to the specified path
        Stream.SaveToFile FilePath, 2 ' 2 = overwrite if file exists

        ' Close the stream
        Stream.Close

        MsgBox "File downloaded successfully!"

    Else
        MsgBox "Failed to download the file. HTTP Status: " & HTTP.Status
    End If

    ' Clean up
    Set HTTP = Nothing
    Set Stream = Nothing

End Sub

I am getting the following error message

I woudl appreciate help in getting this to work.

Thank you!

Answer 1

blob:https://wowa.ca/c9c80084-6aa5-447a-8c54-3b1c58ab67f3 is not valid url

try replace it with http://e.anyoupin.cn/ceshi/viewdocx/docx/2.xlsx for testing your codes .

Answer 2

Appreciated but it still does not work.

I am not getting the following error message:

Still looking for a fix!

Thanks.

Answer 3

Appreciated but it still does not work.

I am not getting the following error message:

This is because http://e.anyoupin.cn/ceshi/viewdocx/docx/2.xlsx

has been blocked by your system.

Can you visit it by Edge web browser?

try

https://e.anyoupin.cn/ceshi/viewdocx/docx/2.xlsx

Still looking for a fix!

Thanks.

The codes did download the file to my local disk drive as expected when I tested on my side.

Sub DownloadHistoricalPrimeRates()

    Dim URL
    Dim FilePath
    Dim HTTP
    Dim Stream

    ' URL of the file to download
    URL = "http://e.anyoupin.cn/ceshi/viewdocx/docx/2.xlsx"

    ' Path where the file will be saved (include file name and extension)
    FilePath="D:\wwwroot\HA626046\WEB\Bsbm\stu60\vba\up\upload\22.xls"

    ' Create XMLHTTP object for file download
    Set HTTP = CreateObject("MSXML2.XMLHTTP")

    ' Create ADODB Stream object to write the file
    Set Stream = CreateObject("ADODB.Stream")

    ' Initialize request to download the file
    HTTP.Open "GET", URL, False
    HTTP.send

    ' Check if the request was successful (status code 200)
    If HTTP.Status = 200 Then

        ' Open the stream and set properties
        Stream.Open
        Stream.Type = 1 ' Binary
        Stream.Write HTTP.responseBody

        ' Save the downloaded file to the specified path
        Stream.SaveToFile FilePath, 2 ' 2 = overwrite if file exists

        ' Close the stream
        Stream.Close

        MsgBox "File downloaded successfully!"

    Else
        MsgBox "Failed to download the file. HTTP Status: " & HTTP.Status
    End If

    ' Clean up
    Set HTTP = Nothing
    Set Stream = Nothing

End Sub
DownloadHistoricalPrimeRates()

Answer 4

Appreciate all the assistance noting:

I copied your code into a brand new workbook noting I still cannot reach the file as noted by:

1. I cannot reach it via Microsoft Edge. I get the following:

2. The script is chocking at th send command as noted by

The error message I get is as follows:

Thanks.

Answer 5

Additional update.

I tried a link that can access the file target file (i.e., I get the MSFT Edge dialogue box asking me to save the file).

The code still chokes at the HTTP.send command.

Thank you.