Download zipped csv file after extracting it

Question

I have the url which contains a zipped csv file, I need to download only .csv from the zip , the Codes below is downloading zip

Sub DownloadFile()

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

    Dim WinHttpReq As Object
    Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
    WinHttpReq.Open "GET", myURL, False, "username", "password"
    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\playt\Desktop\STACK\ruff.zip", 2 ' 1 = no overwrite, 2 = overwrite
        oStream.Close
    End If

End Sub

Answer 1

You cannot download a file from an archive, but you can adapt your code in the next way:

Insert this lines at the end of your existing code (just before End Sub):

Dim zipFileName As String, unZipFolderName As String
  zipFileName = "C:\Users\playt\Desktop\STACK\ruff.zip"

  unZipFolderName = left(zipFileName, InStrRev(zipFileName, "\") - 1)
  UN_Zip zipFileName, unZipFolderName

The simplest sub able to unzip and delete the archive must look like that (it needs a reference to 'Microsoft Shell Controls And Automation'):

Private Sub UN_Zip(zipFileName As String, unZipFolderName As String)
     Dim wShApp As New Shell

     wShApp.Namespace(unZipFolderName).CopyHere wShApp.Namespace(zipFileName).Items
      Kill zipFileName
 End Sub

If too lazy for adding the reference, to make the code look more elegant, the sub can be adapted with only two lines:

Use Dim wShApp As Object instead of Dim wShApp As New Shell declaration and then add the next line:

Set ShApp = CreateObject("Shell.Application")

In this way, no reference is necessary, anymore...