Download and extract zip file, and open file in it

Hello!

First of all thank for your time guys, I really preciate it.

As an introduction, I work as an Economic Consultant, and we usually track a lot of data series. In order to keep updated and simplifying the task of updating our databases, we use macros in our excels where your click and it download from national statistics sources the original excel file where data is published and updates ours. But, I'm stucked with one file where data is uploaded as an excel inside a zip file.

So, I have the link where the zip file is uploaded monthly (they don't change the link). I need a macro to download the zipfile, and open the excel file in it (and then I continue working in it). I didn't say extract, because I don't need the file to be extracted and saved in an specific folder, I just need the file opened in order to copy data and paste in my excel, although I understand that extracting might be necessary.

I have searched online, and I have tried many codes that are posted for extracting files, downloading files, but I am not as fluent with vba in order to adapt it to my necesities: sometimes I got errors, another codes use a popup window asking you to choose the file (I want that to be already determined), and things like that.

Thank you in advance for helping!!

if it is of any help, here's the link: https://www.agroindustria.gob.ar/sit...ues%202018.zip

and my version of office is 2010.

Hallo, first of all, it is possible. I hesitate to show a code here, because there are undocumented commants and it could reach a level, MS may want to hide (UUID's in xlsx) Use a search-engine for the API URLMON and the keyword "rondebruin, unzip" to extract the data in the zip-file. regards

welcome to the forum.

Code:

---

Private Declare Function URLDownloadToFileA Lib "urlmon" _
    (ByVal pCaller As Long, _
    ByVal szURL As String, _
    ByVal szFileName As String, _
    ByVal dwReserved As Long, _
    ByVal lpfnCB As Long) As Long

Private Function DownloadUrlFile(URL As String, LocalFilename As String) As Boolean
    Dim RetVal As Long
    RetVal = URLDownloadToFileA(0, URL, LocalFilename, 0, 0)
    If RetVal = 0 Then DownloadUrlFile = True
End Function

Sub vbax_63320_download_and_extract_zip_file()
   
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
   
    DownloadUrlFile "https://www.agroindustria.gob.ar/sitio/areas/pesca_maritima/desembarques/_descargar/2018//000000_Desembarques%202018.zip", _
        ThisWorkbook.Path & "\zipfile.zip"
   
    With CreateObject("Shell.Application")
        .Namespace(ThisWorkbook.Path).CopyHere .Namespace(ThisWorkbook.Path & "\zipfile.zip").Items
        Workbooks.Open .Namespace(ThisWorkbook.Path & "\zipfile.zip").Items.Item(0)
    End With

    'code to copy data from downloaded file here

End Sub

---

Thank you for your answers!!

mancubus thank you for your welcome and for the code. I have tried to run it, but I got an error in line

Code:

---

.Namespace(ThisWorkbook.Path).CopyHere .Namespace(ThisWorkbook.Path & "\zipfile.zip").Items

---

After the last With.

the error says:

Quote:

---

Run time error '91':
Object variable or With block variable not set

---

Do you know what is causing it?

Thanks!

Hallo, with a little test, maybe this help:

Code:

---

With CreateObject("Shell.Application")        .Namespace(ThisWorkbook.Path & "\").CopyHere .Namespace(ThisWorkbook.Path & "\zipfile.zip").Items        Workbooks.Open .Namespace(ThisWorkbook.Path & "\zipfile.zip").Items.Item(0)    End With

---

(adding a "" after .Path) regards

Thank you Fennek.

I´ve tried with your modification but I still get the same error. :(

corrected code below.

working file attached.

Code:

---

Sub vbax_63320_download_and_extract_zip_file()
   
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
   
    DownloadUrlFile "https://www.agroindustria.gob.ar/sitio/areas/pesca_maritima/desembarques/_descargar/2018//000000_Desembarques%202018.zip", _
        ThisWorkbook.Path & "\zipfile.zip"
   
    With CreateObject("Shell.Application")
        .Namespace(ThisWorkbook.Path).CopyHere .Namespace(ThisWorkbook.Path & "\zipfile.zip").Items
        Workbooks.Open ThisWorkbook.Path & "\" & .Namespace(ThisWorkbook.Path & "\zipfile.zip").Items.Item(0)
    End With

    On Error Resume Next
    Kill ThisWorkbook.Path & "\zipfile.zip" 'to delete the downloaded zip file. remove this line if you want to keep it.
    On Error GoTo 0



    'code to copy data from downloaded file here


End Sub

---

Hi mancubus, thank you for the reply.

I do still have the same error with your file, I don't know why it works for you but not for me. Do you think that there might be some setting that is wrong specified in my pc?

Also, when I go step by step and it goes through the part of the function, when:

Code:

---

    RetVal = URLDownloadToFileA(0, URL, LocalFilename, 0, 0)
    If RetVal = 0 Then DownloadUrlFile = True

---

It doesn't go to DownloadUrlFile = True, so RetVal is taking a value different from zero. I don't know if it is supposed to do that, or that mighy be related with my error.

Thanks for your kindness!

is your machine 64 bit windows?

if so

Code:

---

PrivateDeclare PtrSafe Function URLDownloadToFile Lib"urlmon" _
      Alias"URLDownloadToFileA"( _
        ByVal pCaller As LongPtr, _
        ByVal szURL AsString, _
        ByVal szFileName AsString, _
        ByVal dwReserved As LongPtr, _
        ByVal lpfnCB As LongPtr) As Long

---

Yes, my machine is 64 bit windows. I tried your correction, but nothing changed.

Quote:

---

Originally Posted by mancubus

is your machine 64 bit windows?

if so

Code:

---

PrivateDeclare PtrSafe Function URLDownloadToFile Lib"urlmon" _
      Alias"URLDownloadToFileA"( _
        ByVal pCaller As LongPtr, _
        ByVal szURL AsString, _
        ByVal szFileName AsString, _
        ByVal dwReserved As LongPtr, _
        ByVal lpfnCB As LongPtr) As Long

---

---

sorry for the confusion.
this should be related with 64Bit vs 32Bit office programs and not 64Bit vs 32Bit windows.
because you can run 32Bit office programs on 64Bit win machines.

maybe using conditional compilation?????

Code:

---

#If VBA7 Then
' Works in 32/64 bits of Office 2010 and later
    Declare PtrSafe Function URLDownloadToFileA Lib "urlmon" _
        (ByVal pCaller As LongPtr, _
        ByVal szURL As String, _
        ByVal szFileName As String, _
        ByVal dwReserved As LongPtr, _
        ByVal lpfnCB As LongPtr) As LongPtr
#Else
    Declare Function URLDownloadToFileA Lib "urlmon" _
        (ByVal pCaller As Long, _
        ByVal szURL As String, _
        ByVal szFileName As String, _
        ByVal dwReserved As Long, _
        ByVal lpfnCB As Long) As Long
#End If

---

Oh ok I see. I have checked and my office version is 32 bits. But I am still unable to run the code, it continues stopping with the

Quote:

---

Run time error '91':
Object variable or With block variable not set

---

error. :crying::crying:

I've used the conditional compilation but nothing changed.

it may be related with your internet security settings.

Mmm, I have tried turning off the firewall of windows, and putting all settings in the Trust Center in excel in the most free way, but nothing. :(