[Excel VBA] How to Compress Multiple Files into a ZIP Archive (Shell.Application)

It is common to want to combine multiple report files generated by macros into a single ZIP file for email attachments or backups.

Although VBA does not have a direct command to manipulate ZIP files, you can compress files by controlling the Windows “Shell” features from VBA.

This article explains practical code for adding specific files to a single ZIP file using the Shell.Application object.

Important Prerequisites

Two preparations are required to run this macro:

1. Reference Setting: Open Tools > References in the VBA Editor and check “Microsoft Shell Controls And Automation”. This allows you to smoothly use Shell features in VBA.
2. Empty ZIP File: You must create an empty ZIP file beforehand to serve as the “container” for the compressed files.

Complete VBA Code

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

    ' Declare variables
    Dim shellObj As New Shell32.Shell
    Dim zipFile As Shell32.Folder
    Dim sourceFile As Variant
    Dim filesToAdd As Variant
    Dim zipFilePath As String
    
    '--- Settings ---
    ' Path to the empty ZIP file
    zipFilePath = ThisWorkbook.Path & "\Backup.zip"
    ' Array of files to add to the ZIP
    filesToAdd = Array("Report_A.xlsx", "Report_B.xlsx", "Summary.docx")
    '--- Settings End ---
    
    ' Note: Ensure an empty ZIP file exists at zipFilePath before running this

    ' Get the ZIP file as a Folder object
    Set zipFile = shellObj.Namespace(zipFilePath)
    
    '--- 1. Add specified files to ZIP one by one ---
    For Each sourceFile In filesToAdd
        ' Copy (compress) the file into the ZIP using CopyHere
        zipFile.CopyHere ThisWorkbook.Path & "\" & sourceFile
        
        '--- 2. Wait for compression to complete ---
        ' Compression is asynchronous, so we must wait
        ' (Simple check: waiting for item count to reach 1 for the first file, etc.)
        ' *For strict logic, compare file counts before and after adding.
        Do Until zipFile.Items.Count = 1 
            Application.Wait Now + TimeValue("00:00:01")
        Loop
    Next sourceFile

    MsgBox "Files have been compressed into the ZIP archive."

End Sub

Key Points of the Code

1. Shell.Application Object

Shell.Application is an object that allows you to control basic OS functions (the Shell), such as the Windows Desktop or File Explorer, directly from VBA.

2. .Namespace(Path) Method

shellObj.Namespace(zipFilePath) The .Namespace method retrieves a specified path as a Folder object that VBA can manipulate. The great advantage of this method is that it treats ZIP files exactly like regular folders.

3. .CopyHere Method

zipFile.CopyHere "Path to the file you want to copy" The .CopyHere method of the Folder object copies a specified file into that folder. In this case, since zipFile represents a ZIP archive, executing this method compresses the file and adds it to the archive.

4. Waiting for Compression to Complete

Do Until zipFile.Items.Count = 1
    Application.Wait Now + TimeValue("00:00:01")
Loop

This is the most critical part of this code. The file compression by .CopyHere runs asynchronously (in the background) from the main VBA process.

If you do not wait, VBA will proceed to the next loop immediately, potentially causing errors when trying to compress multiple files simultaneously.

To prevent this, a Do Until loop is used to wait for 1 second until the number of items in the ZIP file increases (indicating compression is complete). By ensuring each file is fully processed before moving to the next, you avoid errors.

Summary

The steps to compress files into a ZIP using the Shell.Application object are as follows:

1. (Recommended) Set a reference to “Microsoft Shell Controls And Automation”.
2. Prepare an empty ZIP file as the container.
3. Create a Shell.Application object.
4. Retrieve the ZIP file as a Folder object using the .Namespace method.
5. Use a loop to add files one by one with the .CopyHere method.
6. Insert a wait process after each .CopyHere to ensure compression completes.

This technique allows you to build practical workflows, such as grouping multiple outputs created by VBA into a single ZIP file at the end of a process.