• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5185
  • Last Modified:

ZIP a file with VBA and wait until it's zipped

I would like to ZIP a file with VBA. The following code from Ron de Bruin:

Unfortunately the following code doesn't work all right. I get an endless loop.
        'Keep script waiting until Compressing is done
        On Error Resume Next
        Do Until oApp.NameSpace(FileNameZip).items.Count = I
            Application.Wait (Now + TimeValue("0:00:01"))

In another thread a similar topic is shown:
I have gone over this one and corrected my code accordingly.

In advance thank you very much for your help

1 Solution
What are you compressing ? data in memory of a file of some sort ?

If you want to compress a file use the free zlib library
The link above contains a vb wrapper for the zlib library, Im pretty sure the calls are blocking ....so they won't return execution to your script/app until they finish.
reneleisibachAuthor Commented:
The files to be compressed are XLS. It could be just one or even houndreds.

Is it possible to unblock the calls? Is there no feedback from the compression?
How about the expression 'oApp.NameSpace(FileNameZip).items.Count = I'?
Of course 'I' stands for the amount of files to be compressed.

I would like to avoid additional installations of DLLs.

In advance thank you for your answer.

VB doesn't have any native support for doing compression unfortunately.
So the best way is would really be to use zlib or some other 3rd party component.
I recommended zlib - http://www.zlib.net/  as it is tried and tested, has been around for a long time and is free.  

http://www.paradoxes.info/code/ZipExtractionClass.html has code which makes using the zlib dll very easy from vb or vba.

I haven't tried it in vba but it should work, just stick it in a new class module. You will get a few errors on the raise event lines. Just delete them.

The zlib dll won't actually need to be registered like an active x component it just needs to be available in the path, either with the access.exe or somewhere in the path environment variable like c:\windows\

You should now be able to use it as described in the link above to compress multiple files, extract etc.

Sorry to keep banging on about zlib but I reckon it's your best bet.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Rey Obrero (Capricorn1)Commented:
this will require WinZip.

    dim source As String
    dim target As String
    source = "C:\MyXl.xls"
    target = "C:\MyXl.zip"
    Shell "C:\Program Files\WinZip\WINZIP32.EXE -a -r " & target & " " & source
reneleisibachAuthor Commented:
Actually I don't have WinZip and the compress function would be used on different computers.
So I would  rather rely on something I can give to the client.

Now I am trying to implement the solution of Wikkard:

Now I only have the problem left of how to make the class ZipClass reachable for:
Dim z As ZipClass
I have to implement this in Access-VBA.

reneleisibachAuthor Commented:
So far I tried to put the code of the two CLS files into modules. But it doesn't work.
How can I include CLS files in Access2000 or Access2003?

In avance thank you very much for the response.

Rene Leisibach
reneleisibachAuthor Commented:
I just found a solution to my original code at the top.
With the main purpose of adding XLS files to a empty zip it is best to use the function 'DoEvents'. I feel so silly I haven't thought about it before.

According the original code of Ron de Bruin it might be best to add 'DoEvents' after the line:
    oApp.NameSpace(FileNameZip).CopyHere FileNameSingle
    i = DoEvents

Thank you everyone

Rene Leisibach
Uuups, can I put my comment as solution???
reneleisibachAuthor Commented:
Dear mbizup

I agree to close this case. Thank you.

PAQed with points refunded (250)

EE Admin

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now