VBA Zip File error

Question

I am using code similar to the below to add some files to a zip folder:

Set oApp = CreateObject("Shell.Application")
oApp.Namespace(CVar(strDest)).CopyHere CVar(strSource)

This works fine most of the time, but sometimes I get an error:

"(Compressed (zipped) Folders Error) Cannot create output file". This error is raised asynchronously outside of my VBA code and as such I cannot trap it to take remedial action.

If I enter break mode and step back to:

oApp.Namespace(CVar(strDest)).CopyHere CVar(strSource)

then the file is added correctly.

I am guessing that there is some kind of lock either on the compressed folder or the source file that is causing this problem, but I am unsure as to how to check this. I should note that the files are being added are pdf files created from Microsoft Access 2007 and we are using the fully qualified paths and the code runs as follows creating up to 10 pdfs per zip file:

1. Create Zip

2. Run this loop:

  For Each ReportToRun
    CreatePdf ' using DoCmd.OutputTo acOutputReport, "rptHame", acFormatPDF, strReportName
    AddToZip
  Next

Any idea how to either fix this or trap the Cannot create output file error? Thanks

Answer 1

I've very successfully used the open souurce Info Zip DLLs. See Compression DLLs, OCXs, etc for links and sample VB code.

Answer 2

Two thoughts, neither likely to fix the issue:

1. Have you tried explicitly providing FALSE as the next argument, i.e., the AutoStart argument? The help file say it defaults to FALSE, but maybe it's opening the PDFs after generating them, so that they are still open?

2. add a SLEEP of a couple of seconds (see Make code go to Sleep for the code).

3. alternatively, loop through all your reports and generate all of them, then start a new loop to copy each one into the zip file, rather than generate/copy for each report. Having a couple of seconds sleep between the two loops might not hurt (assuming that the problem is that the asynchronous PDF generation process is not fully complete at the point the copy is initiated).