Download zip file from web using excel vba

Question

I am new to excel and I am following this link to download zip file using excel vba.

        UrlFile = "https://www1.nseindia.com/content/historical/EQUITIES/2020/MAR/cm13MAR2020bhav.csv.zip"
        On Error GoTo exit_
        With CreateObject("MSXML2.XMLHTTP")
         .Open "GET", UrlFile, False
         .setRequestHeader "Upgrade-Insecure-Requests", "1"
         .setRequestHeader "Sec-Fetch-Dest", "document"
         .send
        If .Status <> 200 Then Exit Function
        b() = .responseBody
        FN = FreeFile
        Open PathName For Binary Access Write As #FN
        Put #FN, , b()
    exit_:
         MsgBox Err.Description
        If FN Then Close #FN
        Url2File = .Status = 200
      End With

On executing .send in above code, it always results in the error "The download of specified resource has failed" .Kindly help me with this issue.

Does this answer your question? [How do I download a file using VBA (without Internet Explorer)](https://stackoverflow.com/questions/17877389/how-do-i-download-a-file-using-vba-without-internet-explorer) — Praburaj, Apr 07 '20 at 10:53
I have used 'Microsoft.XMLHTTP' also and my code is similar to the code in the given link. But I am getting error the download has failed. — vaishali, Apr 07 '20 at 11:06

score 2 · Accepted Answer · answered Apr 07 '20 at 11:25

Public Function DownloadFile()

Dim myURL As String
myURL = "https://www1.nseindia.com/content/historical/EQUITIES/2020/MAR/cm13MAR2020bhav.csv.zip"

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False
WinHttpReq.send

If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile "C:\Users\praburaj\Downloads\file.zip", 2 ' 1 = no overwrite, 2 = overwrite
    oStream.Close
End If

End Function

Download zip file from web using excel vba

1 Answers1