Discussion Locked

Automate zipping files in VBA

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I would like to write a program in Excel/Outlook VBA to automate the process of zipping files in multiple folders on a public drive on the server. Currently I have to manually zip the files in each folder. I would appreciate any hints on the keywords/commands that I can get started with to automate the process in Excel VBA.

Many thanks in advance!!

5 replies

by roho

There are two solutions possible to this problem:

You either use the Shell commend to execute a command line zip program (could be the good old PkZip.exe, or WinZip). You will have to find out what the exact commandline options are for your particular purpose.

The other possibilty is pickup a zip DLL from somewhere. You can download zip32.dll from http://home.planet.nl/~heuve345/freesoft/downloads.html
You can incorporate that into your code.

After this the sky is the limit.

by kemaine

Thanks for the suggestion. I downloaded the winzip command ad-on program, tried the shell command in excel VBA and it run the DOS command prompt successfully. The sample VBA code is: Shell "command.com /c d:\programs\winzip\wzzip c:\test\Client_Listing c:\test\client_listing.xls" However, my additional question is: how to replace the zip file name and source file name with strings/variables so that I can use a loop to create multiple zip files without having to type the individual names in each command?

I did some tests in this command line but got some procedural errors.

This discussion is closed to new replies.