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.
If you want to start to use the software as soon as possible, then you can:
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.
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.
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