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

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse

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

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

For example code to unzip a zip file with 7-Zip visit :
Unzip a zip file with 7-Zip (VBA)

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 here to open the txt file

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 if you need help.

Ron de BruinExcel Automation