I know, I know, you’ve already seen code that can zip and unzip files. Things like:

• VBA Zip/Unzip/Compress/Extract
• VBA code to Zip and Unzip files and folders
• Windows Zipping for Ms-access
• Compression and decompression methods for VBA

but, I thought, that perhaps we could explore a newer, and perhaps easier, approach today.

PowerShell to the Rescue!?

A short while back, for fun, I explored invoking PowerShell commands through VBA and created a series of procedures and shared them in my article entitles VBA – Run PowerShell Command.  Since then, I’ve furthered my education in PowerShell and have had my eyes opened to its true power and simplicity (once you understand the basics).  PowerShell basically can do everything on a PC (Networking, Hardware, ActiveDirectory, Events, …), once you know which cmdlet to use!

So I thought I’d take a second look at a common need zipping/unzipping and see if PowerShell could possibly simplify a developers life.

What I found out was the fact that PowerShell can perform either of these action using a single line of code!  No APIs, dlls or convoluted modules.

So combining the PS_Execute() sub from VBA – Run PowerShell Command with either of these commands and we have the ability to create zip files or extract the content of a zip file with 1 line of code.

Below are 2 procedures I created to illustrated how this can be done.

File Zipping/Compression with PowerShell through VBA

To Zip/Compress a File, or an entire folder, you can use the Compress-Archive PowerShell cmdlet. With a single line, you’re done! The basic syntax is:

Compress-Archive -LiteralPath SourcePath -DestinationPath DestinationPath

With this information, we need only create a wrapper procedure to build the PowerShell command and pass it to my PS_Execute sub.

'---------------------------------------------------------------------------------------
' Procedure : PS_Zip
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Zip up a file or folder
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Requires a copy of the PS_Execute() sub
' References: https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.archive/compress-archive?view=powershell-7.1
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sSrc              : The source file or folder to compress/zip
' sDest             : The output zip file (fully qualified path and filename)
' sCompressionLvl   : Compression level to be used
'                       NoCompression, Fastest or Optimal
'
' Usage:
' ~~~~~~
' Compress a single file
'   PS_Zip("C:\Temp\MonthlyStats.xlsx", "C:\Users\Dev\Desktop\MyZipFile.zip")
' Compress a whole folder
'   PS_Zip("C:\Temp\", "C:\Users\Dev\Desktop\MyFolder.zip")
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2021-10-12              Initial Release
'---------------------------------------------------------------------------------------
Public Sub PS_Zip(sSrc As String, _
                  sDest As String, _
                  Optional sCompressionLvl As String = "Optimal")
    On Error GoTo Error_Handler
    Dim sCmd                  As String

    sCmd = "Compress-Archive -LiteralPath '" & sSrc & "' -DestinationPath '" & sDest & _
           "' -CompressionLevel " & sCompressionLvl
    Call PS_Execute(sCmd)

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: PS_Zip" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

You’ll also notice that this procedure includes a 3rd optional argument that enables us to specify the compression level to be used. Something you don’t see in most other approaches.

Adding File(s) to an Existing Zip File using PowerShell through VBA

Should you already have a zip file and merely want to add new file(s) to it, this can easily be achieved using the -Update argument.

The basic syntax being:

Compress-Archive -Path File(s)ToAdd - Update -DestinationPath DestinationPath

Thus, we can create the following VBA procedure:

'---------------------------------------------------------------------------------------
' Procedure : PS_AddToZip
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Add file(s) to an existing Zip file
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Requires a copy of the PS_Execute() sub
' References: https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.archive/compress-archive?view=powershell-7.1
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sZipFile          : The zip file (fully qualified path and filename) to add the file(s)
'                       to.
' sCSVFileToAdd     : comma separated listing of the file(s) to add to the zip file.
'
' Usage:
' ~~~~~~
' Add a single file
'   PS_AddToZip "C:\temp\cut.zip", "C:\temp\aa.jpg"
' Add multiple files
'   PS_AddToZip "C:\temp\cut.zip", "C:\temp\aa.jpg, C:\temp\aa_gray.jpg,C:\Temp\Book1.xls"
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2025-01-28             Initial Release
'---------------------------------------------------------------------------------------
Public Sub PS_AddToZip(sZipFile As String, _
                  sCSVFileToAdd As String)
    On Error GoTo Error_Handler
    Dim sCmd                  As String
    Dim aFiles() As String
    Dim sFiles As String
    Dim lCounter As Long
    
    aFiles = Split(sCSVFileToAdd, ",")
    For lCounter = 0 To UBound(aFiles())
        If lCounter > 0 Then sFiles = sFiles & ", "
        sFiles = sFiles & "'" & Trim(aFiles(lCounter)) & "'"
    Next lCounter
    
    Debug.Print sFiles

    sCmd = "Compress-Archive -Path " & sFiles & " -Update -DestinationPath '" & sZipFile & "'"
    Debug.Print sCmd
    Call PS_Execute(sCmd)

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: PS_AddToZip" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

File Unzipping/Expansion with PowerShell through VBA

Similarly to compressing, unzipping is equally simple, the only difference is that we must use the Expand-Archive cmdlet.

Expand-Archive -LiteralPath SourceFile -DestinationPath DestinationPath

Just like with zipping, we need only create a wrapper procedure to build the necessary PowerShell command and pass it to my PS_Execute sub.

'---------------------------------------------------------------------------------------
' Procedure : PS_UnZip
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Unzip a file
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Requires a copy of the PS_Execute() function
' References: https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.archive/expand-archive?view=powershell-7.1
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sSrc      : Zip file to unzip/expand
' sDest     : Folder where it should be to extracted to
'
' Usage:
' ~~~~~~
' Call PS_UnZip("c:\temp\testing.zip", "c:\temp\exports")
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2021-10-12              Initial Release
'---------------------------------------------------------------------------------------
Public Sub PS_UnZip(sSrc As String, sDest As String)
On Error GoTo Error_Handler
    Dim sCmd                  As String

    sCmd = "Expand-Archive -LiteralPath '" & sSrc & "' -DestinationPath '" & sDest & "'"
    Call PS_Execute(sCmd)

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: PS_UnZip" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

The Power of VBA with PowerShell is Simply Undeniable!

As you can see with both examples above, with 2 lines (could actually be a single line), you can now zip/unzip to your hearts content. Doesn’t get much simpler than that.

I should also note that, on my Windows 10 machine, these cmdlets were both installed by default, so this code should work universally.

The ability to easily invoke PowerShell commands from VBA is truly extraordinary and can open up entire new worlds to you as a developer.

A Few Resources on the Subject

• VBA – Run PowerShell Command
• Compress-Archive (Microsoft.PowerShell.Archive) – PowerShell | Microsoft Docs
• Expand-Archive (Microsoft.PowerShell.Archive) – PowerShell | Microsoft Docs