This add-in is really a front end that supports the command line versions of PKZip and WinZip. It relies on either of these programs to do the actual compression of the file. Thus, you must have the command-line version of either PKZip or WinZip. If you have a registered copy of WinZip, you can download the free command-line component from this site.  PKZip includes the command-line program as part of the standard package. No upgrade should be necessary. You must have the command line version of PKZIP or WinZip installed on your machine for this add-in to work. The file name of the command line version of PKZip is "PKZIPC.EXE". The command line version of WinZip is "WZZIP.EXE".

To use the add-in, first download this file and save it to the folder of your choice, and unzip the file. Then open Excel, and choose "Add-Ins" from the Tools menu. (In Excel 2007, click on the main Excel button, choose Excel Options, then Add-Ins, and then Go.).  In the Add-Ins dialog, click Browse and navigate to the file you saved and click Open. This will load the add-in. When the add-in is loaded, it will add an item to your Tools menu (or an item to the Add-Ins Ribbon in Excel 2007) called "Save Copy And Zip".
 

When you select "Save Copy And ZIp" from Tools menu (or the Add-Ins Ribbon in 2007), you will see the dialog shown at the left. The list box will display the full file names of all open workbooks.  Select from this list the workbook you want to zip. By default, the zip file will be created in the same folder as the selected workbook, and will have the same name, except that it will have a "zip" extension rather than an "xls" extension. You can change the name and/or location of the zip file to be created by click the Browse button to the right of the Zip File Name text box.

You may choose "<selection>" to copy the current selected range to a workbook with a single worksheet and zip that file.

If the "Delete Existing Zip File" checkbox is checked, the existing zip file is deleted without warning. If this checkbox is not checked, you will be asked whether you want to delete the existing zip file. If you click "No" to this prompt, the process will terminate. If you click "Yes", the existing zip file will be deleted and replaced by the new zip file.

If "Include Date And Time In File Name" is checked, the current date and time, in "short date" and "short time" formats, are included in the file name.

The file to be zipped must have been saved to disk -- i.e., it must have a Path name. The add-in does not support new, unsaved files.

Internally, the program uses the the FindExecutable Windows API function to determine what program is associated with the "zip" file extension. It looks in the folder that contains this executable for the command line version of that program. In PKZip, this program is called "PKZIPC.EXE". In WinZip, the program is called "WZZIP.EXE". If WinZip is the program associated with "zip" files, and the command line version of WinZip cannot be found, the code will search the Path environment variable to determine if PKZip is installed. If PKZip is installed, the program uses its command line version. If no executable is associated with "zip" files, the program will search for PKZip. If a program other than PKZip or WinZip is associated with "zip" files, the program will attempt to find PKZip. If it finds it, it will zip the file with PKZip. If PKZip cannot be found, the add-in will be unable to zip the file. Only PKZip and WinZip are supported by the add-in.

Once the proper program to zip the file is found, the add-in does a SaveCopyAs operation to the temporary folder whose name is returned by the GetTempPath API function. Each user of a machine will have his own Temp file. After the SaveCopyAs operation, the program calls ShellAndWaitSimple to execute the zip program and wait until the zip operation is complete. It then deletes the temporary Excel file. The actual Excel file open in Excel is not altered in any way.

In addition to the userform interface, you can call the code directly if you wish to automate the process. The function that does the actual work is

Public Function SaveCopyAndZipFile(WorkbookName As String, _
    Optional ZipFileName As String = vbNullString, _
    Optional DeleteExistingZipFile As Boolean = False) As Boolean

Where WorkbookName is the name of the open workbook to save, ZipFileName is the name of the zip file, and DeleteExistingZipFile indicates whether to automatically delete the original zip file if it exists. For example, you can call this from another project that has a reference to the add-in's project with code like the following:

Dim Res As Boolean
Res = SaveCopyAndZip.SaveCopyAndZipFile(WorkbookName:="Book1.xls", _
                ZipFileName:="C:\MyZip.zip", DeleteExistingZipFile:=True)
If Res = True Then
    MsgBox "File successfully zipped"
Else
    MsgBox "An error occurred"
End If

If you omit ZipFileName, the function will use the workbook name, but with an extension "zip" rather than "xls".

This add-in has been tested in both Excel 2003 and 2007. It requires Excel 2000 or later. It will not work in Excel 97.

The project is completely unprotected. You are free to examine and modify the code as you wish.

Save Copy And Zip As A COM Add-In

A new version of SaveCopyAndZip is now available as a COM Add-In for Excel 2000 and later.  This version does not required that you have PKZip or WinZip. The zipping engine is built in to the program itself, using Moonlight Software's VBSecure component.  This version adds optional password protection to your zip files for added security.