PDA

View Full Version : Download and extract zip file, and open file in it



facu_uli
07-31-2018, 01:15 PM
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/sitio/areas/pesca_maritima/desembarques/_descargar/2018//000000_Desembarques%202018.zip

and my version of office is 2010.

Fennek
08-01-2018, 01:43 AM
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

mancubus
08-01-2018, 02:00 AM
welcome to the forum.



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

facu_uli
08-01-2018, 06:43 AM
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

.Namespace(ThisWorkbook.Path).CopyHere .Namespace(ThisWorkbook.Path & "\zipfile.zip").Items
After the last With.

the error says:

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

Do you know what is causing it?

Thanks!

Fennek
08-01-2018, 12:49 PM
Hallo, with a little test, maybe this help:
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

facu_uli
08-01-2018, 01:27 PM
Thank you Fennek.

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

mancubus
08-02-2018, 05:24 AM
corrected code below.

working file attached.



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

facu_uli
08-02-2018, 07:01 AM
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:


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!

mancubus
08-02-2018, 08:02 AM
is your machine 64 bit windows?

if so


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

facu_uli
08-03-2018, 10:36 AM
Yes, my machine is 64 bit windows. I tried your correction, but nothing changed.

mancubus
08-05-2018, 11:16 PM
is your machine 64 bit windows?

if so


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.

mancubus
08-06-2018, 12:06 AM
maybe using conditional compilation?????



#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

facu_uli
08-06-2018, 07:31 AM
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
Run time error '91':
Object variable or With block variable not set error. :crying::crying:

facu_uli
08-06-2018, 07:31 AM
I've used the conditional compilation but nothing changed.

mancubus
08-07-2018, 12:21 AM
it may be related with your internet security settings.

facu_uli
08-07-2018, 01:45 PM
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. :(