Unzip files in folder and subfolders

The VBA macro demonstrated in this article lets you unzip all zip files in a folder to a destination folder you specify. It continues with subfolders until all zip files have been unzipped.
What's on this webpage
1. VBA Code
Actually, there are two macros. Run macro named UnzipFiles to start unzipping files.
'Name macro Sub UnzipFiles() 'Dimension variables and declare data types Dim myfolder As Variant Dim destfolder As Variant 'Ask for a source folder With Application.FileDialog(msoFileDialogFolderPicker) .Show 'Save selected folder path to variable myfolder myfolder = .SelectedItems(1) & "\" End With 'Ask for a destination folder With Application.FileDialog(msoFileDialogFolderPicker) .Show destfolder = .SelectedItems(1) & "\" End With 'Run macro named Recursive with parameters myfolder and destfolder Call Recursive(myfolder, destfolder) End Sub
'Name macro and dimension parameters Sub Recursive(FolderPath As Variant, destfolder As Variant) 'Dimension variables and declare data types Dim Value As String, Folders() As String Dim Folder As Variant, a As Long Dim SApp As Object 'Redimension variable Folders ReDim Folders(0) 'If...Then statement 'Check if last two characters are // and stop macro if true If Right(FolderPath, 2) = "\\" Then Exit Sub 'Dir function returns a String representing the name of a file, directory, or folder, save string to variable Value Value = Dir(FolderPath, &H1F) 'Do Until ... Loop statement Keep iterating lines between Do Until and Loop Do Until Value = "" 'If ... Then ... Else ... Endif statement 'Check if variable Value is equal to . or .. If Value = "." Or Value = ".." Then 'Continue here if variable Value is not equal to . or .. Else 'If ... Then ... Else ... Endif statement 'Check if attribute for file is equal to 16 or 48 indicating it is a folder If GetAttr(FolderPath & Value) = 16 Or GetAttr(FolderPath & Value) = 48 Then 'Save Folder name to variable Folders Folders(UBound(Folders)) = Value 'Add another container to variable Folders, in other words, increase size of array variable Folders by 1 ReDim Preserve Folders(UBound(Folders) + 1) 'Continue here if attribute for file is equal to 16 or 48 indicating it is a folder Else 'Check if last four characters in filename equals .zip If Right(Value, 4) = ".zip" Then 'Unzip file to destination folder Set SApp = CreateObject("Shell.Application") SApp.Namespace(destfolder).CopyHere _ SApp.Namespace(FolderPath & Value).items End If End If End If Value = Dir Loop 'Start macro Recursive for each folder in variable Folders For Each Folder In Folders Call Recursive(FolderPath & Folder & "\", destfolder) Next Folder End Sub
2. Where to put the code?
- Press Alt + F11 to open the Visual Basic Editor (VBE).
- Press with left mouse button on "Insert" on the top menu.
- Press with left mouse button on "Module" to create a new module.
- Paste VBA code to code window.
- Exit VBE and return to Excel.
Note, save your workbook with file extension *.xlsm (macro-enabled workbook).
-
- Press with left mouse button on "File" on the ribbon.
- Press with left mouse button on "Save as...".
- Press with mouse on the drop-down list below the file name.
- Select file extension *.xlsm
- Press with left mouse button on "Save".
3. How to run the macro?
- Press Alt + F8 to open the Macro" dialog box.
- Press with left mouse button on the macro name to select it.
- Press with left mouse button on "Run" button.
4. What happens when you run the macro?
- Select a folder you want to search.
- Select a destination folder. This is where all unzipped files will be copied to.
- Macro unzips all zip files in folder and subfolders.
- Macro ends.
Files and folders category
Today I would like to share a macro that compares the content in two different folders and their subfolders. It […]
I will in this article demonstrate a macro that automatically opens all workbooks in a folder and subfolders, one by […]
This is a follow up to my last post Compare file names in two different folder locations and their subfolders, the obvious […]
The following two macros FindReplace() and Recursive() let you rename files and folders recursively based on a search string. The […]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
Table of Contents List files in a folder and create hyperlinks (VBA) How to navigate quickly in a complex workbook […]
This article demonstrates a VBA macro that saves user input to a given workbook and worksheet programmatically. Macros are great […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
Today I'll show you how to search all Excel workbooks with file extensions xls, xlsx and xlsm in a given folder for a […]
This article demonstrates macros that allow you to search for a text string(s) in multiple worksheets and workbooks located in […]
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
This article explains how to check if Excel files in a given folder are password protected. The image above shows […]
In this blog article, I will demonstrate basic file copying techniques using VBA (Visual Basic for Applications). I will also […]
Excel categories
2 Responses to “Unzip files in folder and subfolders”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Dear Sir
I have a zip file in D:\ , which I want to unzip so used the code to unzip the file(file is csv format).
The macro searches the folder and shows no files to unzip.
However there are two files to unzip which the macro is unable to detect
Please help.
Thank you very much for sharing your codes.