How to unzip with VBA?

[entwined]

Hello experts,

I just want to ask if you could help me in unzipping a zipped file or files with VBA. The zipped files contains excel files and they are located in a folder. The unzipped files should be in the same folder.

Thanks in advance...

[mancubus]

https://www.rondebruin.nl/win/s7/win002.htm

giybf

[Leith Ross]

Hello entwined,

Here is a VBA version that works with Office 2007 and later. However, this macro can not unlock password protected Zip files.

The macro will unzip and copy the files in the given folder from each Zip archive (*.zip) in the same folder. In the macro the folder is "C:\Test". Change this what you using.

' Written: March 15, 2017
' Author:  Leith Ross


Sub UnZipFiles()


    Dim File        As Object
    Dim Files       As Object
    Dim MainFldr    As Object
    Dim MainPath    As Variant
    Dim oShell      As Object
    Dim ZipFile     As Variant
    Dim ZipFldr     As Object
    
        MainPath = "C:\Test"
        
        Set oShell = CreateObject("Shell.Application")
            
        Set MainFldr = oShell.Namespace(MainPath)
        
            Set Files = MainFldr.Items
                Files.Filter 32, "*.zip"
                
            For Each File In Files
                Set ZipFldr = oShell.Namespace(File)
                For Each ZipFile In ZipFldr.Items
                    MainFldr.CopyHere ZipFile.Path
                Next ZipFile
            Next File
            
End Sub