Zip Activeworkbook, Folder, File or Files with 7-Zip (VBA)

Ron de Bruin (last update 17-May-2009)
Go back to the Excel tips page

The basic examples on this page use VBA code to zip the ActiveWorkbook, Folder, File or Files with:

http://www.7-zip.org/

For example code to unzip a zip file with 7-Zip visit :
http://www.rondebruin.nl/7zipwithexcelunzip.htm

For examples for WinZip or the default Windows Zip program see the Zip (compress) section here
http://www.rondebruin.nl/tips.htm

Examples

I have add all the code in a txt file on my site so it is easy to copy it in a module of your workbook.

Click on the link below
http://www.rondebruin.nl/files/code7zippage.txt

1. Ctrl a to select all the code
2. Ctrl c to copy
3. Press the Back button in your browser to go back to this page

Open Excel or make Excel the active program
1. Alt-F11 to open the VBA Editor
2. Insert>Module from the Menu bar
3. Ctrl v to Paste the Code
4. Alt-q to go back to Excel

When you use the shortcut Alt F8 now you can see and run the macros.

Note: I use the .zip extension in my examples but you can also use the extension .7z
If you use the extension .7z you will see that the file size is smaller.

Four examples above you can test without changing the code.
Only if you want to test the macro: B_Zip_Fixed_Folder_And_SubFolders
You must change the string of FolderName to the folder you want to zip in this code line
FolderName = "C:\Users\Ron\Desktop\TestFolder"

Important: Read the comments above and in the code good and after you test the code examples
you can try the commented examples in the first macro Zip_Folder_And_SubFolders_Browse

To test it replace :

    ShellStr = PathZipProgram & "7z.exe a -r" _
             & " " & Chr(34) & NameZipFile & Chr(34) _
             & " " & Chr(34) & FolderName & "*.*" & Chr(34)

With one of the strings below :

    'Zip the txt files in the folder and subfolders, use "*.xl*" for all excel files
    ShellStr = PathZipProgram & "7z.exe a -r" _
             & " " & Chr(34) & NameZipFile & Chr(34) _
             & " " & Chr(34) & FolderName & "*.txt" & Chr(34)

    'Zip all files in the folder and subfolders with a name that start with Week
    ShellStr = PathZipProgram & "7z.exe a -r" _
             & " " & Chr(34) & NameZipFile & Chr(34) _
             & " " & Chr(34) & FolderName & "Week*.*" & Chr(34)

    'Zip every file with the name ron.xlsx in the folder and subfolders
    ShellStr = PathZipProgram & "7z.exe a -r" _
             & " " & Chr(34) & NameZipFile & Chr(34) _
             & " " & Chr(34) & FolderName & "ron.xlsx" & Chr(34)

    'Add -ppassword -mhe of you want to add a password to the zip file(only 7z files)
    ShellStr = PathZipProgram & "7z.exe a -r -ppassword -mhe" _
             & " " & Chr(34) & NameZipFile & Chr(34) _
             & " " & Chr(34) & FolderName & "*.*" & Chr(34)

    'Add -seml if you want to open a mail with the zip attached
    ShellStr = PathZipProgram & "7z.exe a -r -seml" _
             & " " & Chr(34) & NameZipFile & Chr(34) _
             & " " & Chr(34) & FolderName & "*.*" & Chr(34)

7-Zip have also a good help file (7-zip.chm) so if you want to know more read this first
You can find it it this folder : C:\Program Files\7-Zip
There is also a forum on the 7Zip site that you can visit.