How to Unzip file using VBA – Excel Macro?

Extract Zip – Unzip files with VBA

To extract Zip file content, You can use windows default “Compressed (Zipped) Folder” application.

There is no inbuilt object within VBA to do this.

You have to invoke the Windows Compressor using Shell object.

Here is how it is done.

1. VBA Macro – Unzip Files to Folder

You have to give two inputs for this code.

  1. Zip file name with full folder path.
  2. Output folder path.

Copy paste this code to your vba project & modify the inputs.

Important: Include reference to Microsoft Shell Controls and Automation

Sub Unzip()
    'Define Variable Data Types
    Dim zipFileName As String
    Dim unZipFolderName As String
    Dim objZipItems As FolderItems
    Dim objZipItem As FolderItem
    
    'Set Zip File Name & Folder path to Unzip
    zipFileName = "PathToZipFile\FilewithFullPath.zip"
    unZipFolderName = "Extract\To\Path"
    
    'Early Binding Reference
    'Add Tools -> Reference -> "Microsoft Shell Controls & Automation"
    Dim wShApp As Shell
    Set wShApp = CreateObject("Shell.Application")
    Set objZipItems = wShApp.Namespace(zipFileName).items
    
    'Extract: Unzip all Files to Folder
    wShApp.Namespace(unZipFolderName).CopyHere objZipItems
    
End Sub

Press F5 & all the files in the Zip will be extracted to the output Unzip folder.

Note: If this code is run twice, then it will popup a alert message asking permission to overwrite the files.

2. VBA – Unzip Specific File to Folder

This below code will unzip only specific file of Your choice.

Just include the correct filename in the If condition within the for loop.

    'Extract: Unzip only spcific File
    For Each objZipItem In wShApp.Namespace(zipFileName).items
        If objZipItem.Name = "FileNameHere" Then
            wShApp.Namespace(unZipFolderName).CopyHere objZipItem
        End If
    Next

The loop will read thru each file within the Zip archive.

If it matches the file name of Your choice, then it will be extracted to the specified folder.

Would you like to learn more about the Unzip code. Check out more references below.

External References:

  1. Excel VBA Zip and Unzip from Exceloffthegrid – Click here.
  2. Unzip Files with Windows Default Zip Program from Rondebruin – Read here.