Automatically ZIP Excel File on saving...

[Darin Kramer]

Howdie,

I have a rather large Excel file, which will be mailed around a lot. As
such I thought it would be benefical to have the file automatically
zipped (using WINzip, if possible) everytime it is saved. ANy ideas if
this is possible, and how to do it...??

Thanks!!!

D

 

[Ron de Bruin]

Yes this is possible

I have code here
http://www.rondebruin.nl/zip.htm

 

[Chip Pearson]

You could use my SaveCopyAndZip add-in. See
http://www.cpearson.com/excel/SaveCopyAndZip.htm . In addition to the
userform interface, you can call the primary function itself directly from
other VBA code without going through the interface.

In the BeforeClose or BeforeSave event of your workbook, use

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ZipFileName As String
ZipFileName = Left(ThisWorkbook.FullName, _
Len(ThisWorkbook.FullName) - 4) & ".zip"
Application.Run "SaveCopyAndZip.xla!SaveCopyAndZipFile", _
ThisWorkbook.FullName, ZipFileName, True
End Sub

The code supports the command line interfaces to both WinZip and PKZip. If
you have a legal, registered version of WinZip (not a trial version), the
command line interface is a free download from
http://www.winzip.com/prodpagecl.htm . The command line version of PKZip is
included in the standard package.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)