[Excel VBA] How to Unzip (Extract) Files to a Specified Folder

In VBA macros, you may often need to read and process data files (such as CSV or text files) contained inside ZIP archives received from external sources. In such cases, it is much smarter to automate the unzipping process using VBA rather than doing it manually.

In this article, I will explain the VBA code to extract the contents of a specified ZIP file to a specific folder using the Shell.Application object, which was also used in the previous article on creating ZIP files.

[Important] Preparation: Add Reference

To run this macro, you need to open Tools > References in the VBA editor and check “Microsoft Shell Controls And Automation”. This allows you to smoothly use Windows Shell functions within VBA.

Complete VBA Code

Below is the VBA code that extracts “Backup.zip” (located in the same folder as the macro workbook) to a folder named “UnzippedFiles”. If the “UnzippedFiles” folder does not exist, it will be created automatically.

' Reference: Microsoft Shell Controls And Automation
Sub UnzipArchive()

    ' Declare variables
    Dim shellApp As New Shell32.Shell
    Dim zipFile As Shell32.Folder
    Dim destinationFolder As Shell32.Folder
    Dim zipFilePath As String
    Dim extractFolderPath As String

    '--- Settings ---
    ' Path of the ZIP file to unzip
    zipFilePath = ThisWorkbook.Path & "\Backup.zip"
    ' Path of the destination folder
    extractFolderPath = ThisWorkbook.Path & "\UnzippedFiles"
    '--- End Settings ---
    
    ' Create destination folder if it does not exist
    If Dir(extractFolderPath, vbDirectory) = "" Then
        MkDir extractFolderPath
    End If
    
    '--- 1. Get ZIP file and destination folder as objects ---
    Set zipFile = shellApp.Namespace(zipFilePath)
    Set destinationFolder = shellApp.Namespace(extractFolderPath)
    
    '--- 2. Copy (unzip) the contents of the ZIP file to the specified folder ---
    ' Specifying "20" executes overwrite without confirmation message
    destinationFolder.CopyHere zipFile.Items, 20

    MsgBox "ZIP file extraction completed."
    
    ' Release objects
    Set destinationFolder = Nothing
    Set zipFile = Nothing
    Set shellApp = Nothing

End Sub

Key Points of the Code

1. Get Folder with Namespace Method

Set zipFile = shellApp.Namespace(zipFilePath)
Set destinationFolder = shellApp.Namespace(extractFolderPath)

The .Namespace method of the Shell.Application object converts not only regular folders but also ZIP files into objects that can be handled by VBA as a type of “folder.” Here, we retrieve both the source ZIP file and the destination folder as objects.

2. Execute Extraction with .CopyHere Method

destinationFolder.CopyHere zipFile.Items, 20

This is the core command that executes the file extraction. The syntax is as follows: [DestinationFolder].CopyHere [ItemsToExtract], [Options]

• zipFile.Items: Specifies the items to extract. By specifying .Items (all items) within zipFile (the ZIP file), we target the entire contents of the ZIP.
• 20: This is an option specifying the behavior during the copy (extraction). Here, we specify 20, which means 4 + 16.
  • 4: Do not display the progress dialog.
  • 16: If a file with the same name exists, overwrite all without a confirmation message.

By specifying option 20, you can achieve a completely silent automatic extraction process (not requiring user response).

Summary

The procedure for unzipping files using the Shell.Application object is as follows:

1. (Recommended) Add a reference to “Microsoft Shell Controls And Automation”.
2. Create a Shell.Application object.
3. Use the .Namespace method to get the source ZIP file and destination folder as objects.
4. Execute [DestinationFolder].CopyHere [SourceZIPContents].
5. (Recommended) Specify 20 in the options to hide confirmation messages.

By using this technique, you can fully automate processes such as automatically unzipping daily reports sent in ZIP format and then reading and aggregating the data inside them using a macro.