Consulting

Results 1 to 16 of 16

Thread: Download and extract zip file, and open file in it

  1. #1

    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.

  2. #2
    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

  3. #3
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    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
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  4. #4

    Question Run time error '91'

    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!

  5. #5
    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

  6. #6
    Thank you Fennek.

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

  7. #7
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    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
    Attached Files Attached Files
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  8. #8
    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!

  9. #9
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    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
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

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

  11. #11
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    Quote Originally Posted by mancubus View Post
    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.
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  12. #12
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    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
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  13. #13
    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.

  14. #14
    I've used the conditional compilation but nothing changed.

  15. #15
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    it may be related with your internet security settings.
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  16. #16
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •