Web Development·11,948 discussions

Automate zipping files in VBA

Locked

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!!

Topic

Automate zipping files in VBA

In reply to Automate zipping files in VBA

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.

Automate zipping files in VBA

In reply to Automate zipping files in VBA

Poster rated this answer

Automate zipping files in VBA

In reply to Automate zipping files in VBA

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.

Automate zipping files in VBA

In reply to Automate zipping files in VBA

Point value changed by question poster.

Automate zipping files in VBA

In reply to Automate zipping files in VBA

This question was closed by the author

[Author]

Replies