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

Posted on 2007-10-16
Last Modified: 2013-11-27
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

Question by:reneleisibach
    LVL 8

    Expert Comment

    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 they won't return execution to your script/app until they finish.

    Author Comment

    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.

    LVL 8

    Expert Comment

    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 -  as it is tried and tested, has been around for a long time and is free. 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.
    LVL 119

    Expert Comment

    by:Rey Obrero
    this will require WinZip.

        dim source As String
        dim target As String
        source = "C:\MyXl.xls"
        target = "C:\"
        Shell "C:\Program Files\WinZip\WINZIP32.EXE -a -r " & target & " " & source

    Author Comment

    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.


    Author Comment

    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

    Author Comment

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

    Author Comment

    Dear mbizup

    I agree to close this case. Thank you.

    LVL 1

    Accepted Solution

    PAQed with points refunded (250)

    EE Admin

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    Article by: Martin
    Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now