How to Easily Manage and Unzip Multiple Archives in Excel

Unzipping a single file is easy. If you have multiple zip files, unzipping them to their respective folders is certainly not an easy task. So, let’s build our own unzipping software in Excel. Each zip file will be extracted to separate folders.

Download Now

If you want to start to use the software as soon as possible, then you can:

Download the Software Now

Otherwise, if you want to DIY, you can read the contents below.

Let’s Prepare the GUI

Create a macro enabled workbook and rename the Sheet1 as “Unzip”. Create headers “ZipFile” and “Destination Folder”. Under the header “ZipFile” you can add as many zip files as you want. Add a destination folder for each zip file. You can also mention the same destination folder for all zip files. Using shapes, add a button on the sheet and name it as “Unzip Files”. As shown in the image, for allowing longer file path to fit on cells, I have merged columns B to N. You can avoid merging columns by just increasing the width of column B.

Let’s make it functional

Copy this script to a new module in your macro enabled workbook. Attach this macro to the button we have created on the sheet “Unzip”.  Populate the sheet “Unzip” with the full path of zip files and their corresponding destination folders. Run the macro by pressing the button “Unzip Files”. If you check destination folder, all files from the zip file would have been extracted and saved there.

Sub Unzip_All_Files()
    Dim OBJ1 As Object
    Dim OBJ2 As Object
    Dim v_variant1 As Variant
    Dim v_variant2 As Variant
    Dim path1 As String
    Dim string1 As String
    Dim zfile1 As String
    Dim lr As Long
    Dim r As Long
    lr = unzip.Range("B" & Rows.Count).End(xlUp).Row
    For r = 5 To lr
        v_variant1 = unzip.Range("B" & r).Value
        If v_variant1 = False Then
        Else
            path1 = Application.DefaultFilePath
            If Right(path1, 1) <> "\" Then
                path1 = path1 & "\"
            End If
            string1 = Format(Now, " dd-mm-yy h-mm-ss")
            v_variant2 = unzip.Range("O" & r).Value
            On Error Resume Next
            MkDir v_variant2
            On Error GoTo 0
            Set OBJ2 = CreateObject("Shell.Application")
            OBJ2.Namespace(v_variant2).CopyHere OBJ2.Namespace(v_variant1).items
            On Error Resume Next
            Set OBJ1 = CreateObject("scripting.filesystemobject")
            OBJ1.deletefolder Environ("Temp") & "\Temporary Directory*", True
        End If
    Next r
End Sub

How does it work?

Using the method “Shell Application”, the macro loops through each listed zip file and unzips files into the destination folder. If the destination folder does not exist, the macro will create it. The macro will stop the loop when it meets an empty row. So please make sure that there are no empty rows before the last used row on the sheet “Unzip”. Please do note, a damaged Excel xlsm file cannot communicate with shell application. You have to then try adding macros and sheet to a new xlsm file.

Modify it

Instead of you listing each zip file on the sheet, allow users to browse and select a folder that contains multi zip files. The macro should then scan the folder for zip files and list it automatically on the sheet. Skip the step of listing destination folder by adjusting the macro to create a folder using the name of the zip file. You can also modify the macro to extract files of specific type or extension to a folder. The macro can also be made to look for specific file name inside zip files and then extract it to the destination folder.  The script can handle zip files but cannot handle WinRAR or 7zip formats.

Author Introduction:

Nick Vipond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Word fix and outlook recovery software products. For more information visit www.datanumen.com