Zip Files from Excel

Vishesh's picture
Copy the following code in a general module and call the TestRun function with the file paths and name as per you requirement. The last parameter is a parameter array - provide file paths of files to be zipped/compressed.
Sub TestRun()
    Call ZipFile("C:\Users\Vishesh\Documents\MyDocuments\Excel VBA\Test", "Zipped1", _
                    "C:\Users\Vishesh\Documents\MyDocuments\Excel VBA\Test\ChartAnimation.xls", _
                    "C:\Users\Vishesh\Documents\MyDocuments\Excel VBA\Test\AVS.txt")
End Sub
Sub ZipFile(strZipFilePath As String, strZipFileName As String, ParamArray arrFiles() As Variant)
    Dim intLoop         As Long
    Dim I               As Integer
    Dim objApp          As Object
    Dim vFileNameZip
    If Right(strZipFilePath, 1) <> Application.PathSeparator Then
        strZipFilePath = strZipFilePath & Application.PathSeparator
    End If
    vFileNameZip = strZipFilePath & strZipFileName & ".zip"
    If IsArray(arrFiles) = False Then GoTo ExitH
'-------------------Create new empty Zip File-----------------

    If Len(Dir(vFileNameZip)) > 0 Then Kill vFileNameZip
    Open vFileNameZip For Output As #1
    Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
    Close #1

    Set objApp = CreateObject("Shell.Application")
    I = 0
    For intLoop = LBound(arrFiles) To UBound(arrFiles)
        'Copy file to Zip folder/file created above

        I = I + 1
        objApp.Namespace(vFileNameZip).CopyHere arrFiles(intLoop)
        'Wait until Compressing is complete

        On Error Resume Next
        Do Until objApp.Namespace(vFileNameZip).items.Count = I
            Application.Wait (Now + TimeValue("0:00:01"))
        On Error GoTo 0
    Next intLoop
    Set objApp = Nothing
End Sub

zip file

Thanks for sharing that is nice.

Sub TestRun when I copy to my vba page it's all red like it is not recognized/error. What did I do wrong?


Vishesh's picture

After copying remove the

After copying remove the blank lines. Actually, its a single line code divided into three lines.

Thanks vishesh :) stupidity

Thanks vishesh :) stupidity of mine not to think twice

If I need to add password in this code for the zip file


Awesome code !!

Works charm, but what If I need to add password in this code for the zip file den where should i insert the code.

Request your help.


Vishesh's picture

I haven't yet been able to

I haven't yet been able to figure out where to add the code to facilitate password. Will surely put the updated code on site once I get it.

Program hangs when he can't

Program hangs when he can't find the file indicated in the array. How can you avoid that hanging?

Vishesh's picture

Pass only those files as

Pass only those files as parameter(s) which exists.

Instead of an array, i have a

Instead of an array, i have a single cell containing a list of files separated by comma, or semi-colon (whichever works). I can't figure out how to change your code to support this structure.

Vishesh's picture

Don't worry about the array.

Don't worry about the array. Just pass the complete file path (NOT just the file name) one after the other separated by comma.

If the cell has a single

If the cell has a single path+file name, it works. but once i add more path+files separated by commas, it hangs and only creates an empty file.