Downloading zip file from website using excel vba (if also able to extract the csv from zip file and open it in excel, then even better)

Question

after quite some searching I've not been able to make a macro that would download a .zip file from a specific website. I mean I've been able to find similar problems but have not been able to apply the changes necessary in order for my problem to be solved. The website that contains the zip files is: https://nio.gov.si/nio/data/prvic+registrirana+vozila+v+letu+2014+po+mesecih, under table header "Priponke" are the files. For example: December 2014 (959 kb), November 2014 (1061 kb), ... The url that downloads the zip file for December 2014 is "cms/download/document/a7605005b6879fe5f7dbab6d60d4ae787dbced6b-1422453741279". I thank you in advance and am awaiting your reply.

My current code is:

Public Sub DownloadFile()
    Dim objWHTTP As Object
    Dim strPath As String
    Dim arrData() As Byte
    Dim lngFreeFile As Long

On Error Resume Next
    Set objWHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
    If Err.Number <> 0 Then
        Set objWHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
    End If
On Error GoTo 0

    strPath = "https://nio.gov.si/nio/data/prvic+registrirana+vozila+v+letu+2014+po+mesecih"
    strPath = "https://nio.gov.si/nio/cms/download/document/a7605005b6879fe5f7dbab6d60d4ae787dbced6b-1422453741279"

    objWHTTP.Open "GET", strPath, False
    objWHTTP.send
    arrData = objWHTTP.responseBody

    If Len(Dir("C:\FootieFile", vbDirectory)) = 0 Then
        MkDir "C:\FootieFile"
    End If

    lngFreeFile = FreeFile
    Open "C:\FootieFile\MyFile.xml" For Binary Access Write As #lngFreeFile
        Put #lngFreeFile, 1, arrData
    Close #lngFreeFile

    Set objWHTTP = Nothing
    Erase arrData
End Sub

Kind regards

Answer 1

You need to download a binary file, and save it. This can be done using the MSXML2.XMLHTTP60 object to download, and ADODB.Stream object for saving.

See e.g. http://www.motobit.com/tips/detpg_read-write-binary-files/

I've used this succesfully to download JPG files from a server and display them in an MS Access front end.

Also I hope you realize you can't start your url with 'cms', as you will need the fully qualified domain name plus resource (aka http:// etc)

Also be careful with unicode data. For that you might need to use StrConv(). Check after downloading that the size of your file is the same as it is on the server.