Go to Top

Zip and Unzip from Microsoft Access VBA

Click image to learn more about the book

On occasions, we have a need to zip files as part of our workflow within Access VBA. One sore point with zipping is that there’s really no simple way to zip or unzip files without depending on a third-party utilities. When you think about it, it is quite odd considering that zipping is built-in to Windows Explorer. (Reading between the lines, it seems to do with licensing constraints).

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

Thankfully, Ron de Bruin has provided a solution which involves automating the Windows Explorer (aka Shell32). A Shell32.Folder object can be either a real folder or a zip folder so by manipulating a zip file as if it was a Shell32.folder, we can then use the “Copy Here” method of the Shell32.Folder to move the files in and out of a zip file.

As Ron has noted, there is a subtle bug when dealing with retrieving a Shell32.Folder via Shell32.Applications’ Namespace method. This code will not work as expected:

According to MSDN documentation, if Namespace method fails, the return value is a nothing and therefore we can get seemingly unrelated error 91 “With or object variable not set”. This is why Ron de Bruin uses a variant in his sample. Converting the string into a variant will work also:

Alternatively, you can choose to early bind by referencing Shell32.dll (typically in WindowsSystem32 folder). In VBA references dialog, it is labeled “Microsoft Shell Controls and Automation”. Early-binding is not subject to the string variable bug. However, our preference is to late-bind as to avoid any problems with versioning that may occur when running code on different computer with different operating systems, service packs and so forth. Still, referencing can be useful for developing & validating your code prior to switching to late binding & distribution.

Another issue we have to handle is that as there is only either “Copy Here” or “Move Here” method available with a Shell32.Folder object, we have to consider how we should handle the naming of files that will be zipped, especially when we are unzipping the files that potentially have the same name or should replace the original files in the target directory. This can be solved in two different ways: 1) unzipping the files into a temporary directory, renaming them, then moving them into the final directory or 2) rename a file prior to zipping so it will be uniquely named when unzipped and thus can be renamed. Option 1 is more safe but requires creating a temporary directory & cleaning up, but when you have control over what the target directory will contain, option 2 is quite simple. In either approach, we can use VBA to rename a file as simply:

Finally, when using Shell32, we are essentially automating the visual aspect of Windows Explorer. So when we invoke a “CopyHere”, it’s equivalent to actually dragging a file and dropping it in a folder (or a zip file). This also means it comes with UI components which may impose some issues, especially when we are automating the process. In this case, we need to wait until the compression has completed before taking any further actions. Because it’s an interactive action that occurs asynchronously, we must write waiting into our code. Monitoring an out-of-process compression can be tricky so we’ve developed a safeguard that covers different contingencies such as compression occurring too quickly or when there is a delay between compression dialog’s progress bar is filling up and it is closing. We do this in 3 different ways; a) timing out after 3 seconds for small files, b) monitoring the zip file’s item count, c) and monitoring the presence of compressing dialog. The last part requires us to use WScript.Shell object’s AppActivate method because unlike Access’ built-in AppActivate, WScript.Shell’s AppActivate will return a boolean value which we can be used to determine whether activation was successful or not, and thus implicate the presence/absence of the “Compressing…” dialog without a messy API handling.

Sample usage
The complete code is given below. To use it, the code would look something like this.

 

Here’s the complete Zip & Unzip procedure; simply copy’n’paste in a new VBA module and enjoy:

An alternative using 3rd party resources

For those who would rather have more control over the process and do not mind including 3rd party utilities, Albert Kallal has a useful sample that allows a true programmatic access to the zipping/unzipping facility and requires no installment beyond copying the 2 DLLs file included wherever the front-end file goes. This is also useful if you do not want any UI components at all (e.g. allowing users to cancel the compression or click “No” to replacing file in a zip file).

32 Responses to "Zip and Unzip from Microsoft Access VBA"

  • Ed Keler
    April 19, 2016 - 7:22 pm Reply

    This code does not work with small files when these small files are added to the archive individually.

    The only options is to archive the entire folder, than all files are picked up from the source directory.

    Set d = sa.NameSpace()
    Set d = sa.NameSpace.Items()
    zip.CopyHere d.items

    Where
    is a directory with files to zip.

    If you have a solution how to add an individual small file (e.g. 1KB ) to a compress folder please share your solution.

    Regards,
    Ed

  • Ed Keler
    April 19, 2016 - 1:22 am Reply

    This solution works only for large files – try archiving files of 1KB, or even 30KB. The code will create a new archive file, but will not add small files into archive.

  • Maury Markowitz
    December 16, 2015 - 9:02 pm Reply

    On my Win10 box you get a “File not found or no read permission” error for new ZIP files. The file is created ok, but the CopyHere returns the error when it attempts to add the file. Oddly, it works fine if you do it again once the file is written. Playing…

    • Maury Markowitz
      December 16, 2015 - 9:12 pm Reply

      Ahhh, found it. The code above is leaving the file open. It closes when the Sub exits, so the next time you run it it works fine. You can fix it by adding a .Close, but the way it’s currently written that’s not obvious. So what I recommend is…

      Set F = FSO.CreateTextFile(ZipFile, True)
      F.Write….
      F.Close

      Presto, works every time.

      • Juan Soto
        September 28, 2016 - 1:41 am Reply

        Thanks Maury!

    • Maury Markowitz
      December 17, 2015 - 10:25 pm Reply

      “onitoring an out-of-process compression can be tricky so we’ve developed a safeguard that covers different contingencies such as compression occurring too quickly or when there is a delay between compression dialog’s progress bar is filling up and it is closing”

      So it seems that the only one of these that is really needed is to check the file count. What are the other two tests for?

      I tried with only this code as the wait-for-end:

      Do While Fld.Items.Count = I
      DoEvents
      Sleep 100
      Loop

      Seems to work OK.

  • ravi
    November 18, 2015 - 9:28 pm Reply

    Hi Ben,

    Thanks for your wonderful post to zip/unzip files.
    By using your code I am able to Zip all the files available in a folder.
    But it zips all the files inside the containing folder as well as folder itself.
    Lets say we have a default folder named X and containing files are file1,file2. And suppose zip folder is Myzip.zip.
    So after zip I find that Myzip.zip folder contains folder X and inside X we have all the files.

    Our requirement is to zip all the files from containing folder not including folder name.

    Can you suggest any approach to zip files only excluding containing folder.

    Thanks

  • Judy Kamber
    February 23, 2015 - 10:55 pm Reply

    We’ve been using this code to ZIP up thousands of image files for over two years. Wonderful.
    It runs in the background on a server in the LAN room. Every once in a long while, a dialog box appears: “Compressed (zipped) folders error – missing or empty ZIP file.” Then everything waits until someone notices, goes into the LAN room and presses enter.

    This is obviously something we need to fix because the LAN room is not monitored regularly. Any suggestions? Thanks!

    • Dawn
      March 16, 2016 - 6:22 am Reply

      HI Judy: did you ever find an answer to this? We are having the same problem 🙁

      • Judy Kamber
        September 28, 2016 - 1:26 am Reply

        No, Dawn, I haven’t found any solution. It happens so rarely but, when it does, it really causes problems. Did you have any luck?

    • Jim Pitfield
      October 11, 2016 - 8:00 pm Reply

      Dawn/Judy you could modify the ErrHandle:, i use it to write to a log file to capture errors with the following sub. I also have it send an email notification if you need that routine let me know i can repost the code. This will record the error but continue the exit routine without prompts

      requires a reference to Microsoft Scripting runtime

      Public Sub WriteErrorLogfile(ErrNum As String, ErrDescription As String)
      ‘Writes log entry when last file is processed sends Log email
      On Error GoTo ErrorHandler:

      Const ForReading = 1
      Const ForAppending = 8

      Dim strFile As String
      Dim strlog As String
      Dim fs, f, c

      strFile = “C:\ErrorLog” & Format(Now(), “yyyymmdd”) & “.txt”
      Set fs = CreateObject(“Scripting.FileSystemObject”)

      ‘if file doesnt exist then create it and write the error
      If fs.FileExists(strFile) = False Then
      Set f = fs.CreateTextFile(strFile, True)
      f.WriteLine (“Error Number: ” & ErrNum & ” Error Description: ” & ErrDescription)
      f.Close
      Else
      ‘file exists so add error to log file
      Set f = fs.OpenTextFile(strFile, ForAppending)
      f.WriteLine (“Error Number: ” & ErrNum & ” Error Description: ” & ErrDescription)
      f.Close
      End If

      ExitRoutine:
      Exit Sub

      ErrorHandler:
      Resume ExitRoutine
      End Sub

      then modify the ErrHandle

      ErrHandler:
      Call WriteErrorLogfile(Err.Number,Err.Description)
      Resume ExitProc

  • Mark Davis
    August 28, 2013 - 7:01 pm Reply

    I am running Access 2007 on an XP sp3 machine and have a licensed copy of WinZip 15 installed as well. I have no issues with Ben’s code as it posted. I’m wondering if the 2002 install has any impact on the operation of 2007. (?) I’ve never been comfortable running Access 2007 (or 2010) with previous versions of Access because of conflicting issues between the variant versions after 2003.

  • alghosh
    August 28, 2013 - 2:39 pm Reply

    Hi Ben,

    Thanks for the reply…

    Set oApp = CreateObject(“Shell.Application”)
    tempshell = oApp.Namespace(CVar(FileNameFolder))

    Set newshell = oApp.Namespace(CVar(Fname)) ‘ error ‘Method “NameSpace” of object ‘IShellDispatch4? failed’ comes here with Fname=”C:\Alankar\macro\test.zip”

    tempshell.CopyHere newshell.Items

    It would be nice if the reason for the same could be identified

  • alghosh
    August 26, 2013 - 6:47 pm Reply

    Hi Ben,
    Thanks for the code.
    I am facing the same issue as Vicki mentioned above. When I run in Win7 its working fine. But when I try to implement in my office system its just not working … Win XP sp3 version 2002 and Access 2007 with WinZip installed. Somehow any *.Zip & Namespace not working , showing error ‘Method “NameSpace” of object ‘IShellDispatch4? failed’. Any help????

    • Ben Clothier
      August 27, 2013 - 11:44 pm Reply

      Hi Alghosh –

      I am not sure if WinZip is involved here but can you please post the calling code that fails so I can try to replicate?

  • Ariel B
    July 13, 2013 - 10:00 am Reply

    Muchas gracias, excelente explicación. Muy útil.
    Atte.

  • Catalin
    June 20, 2013 - 7:26 pm Reply

    Hi, first i want to thank to the author for this code.
    @ Sam Wong: Hello Sam in not an expert in programming but i have in ideea. You can creat an exe (or atctivex) with a flag for creation of txt file. You call the exe with parameters and wait for the flag to become true. After that you go forward with zipping method.

  • judy kamber
    February 21, 2013 - 7:30 pm Reply

    This code is wonderful and so appreciated. I have two questions. When I ZIP the file from a directory like C:abcdef, the ZIP file has the DEF as the path. Then, when I want to unzip to another folder, it first creates a DEF folder then unzips the files inside. How do I either avoid putting the path into the ZIP file or avoid using in when unzipping?

    Second, is there a simple way to show the count of files in the ZIP?

    Thanks again!

    • Sam Wong
      May 2, 2013 - 9:34 pm Reply

      Thank you for posting this great code. I have used it in my Access apps and it works fine most of the time. I occasionally ran into an issue when it failed to grap a target file and ended up with a bad zip file. In my procedure, it first creates a list of long records with fixed length in a temporary table. Then exports to a text file. Next, it runs the ZIP code and transmits the ZIP file remotely to another location. I think it may be the zip function starts out a bid too early before the target file is completely created. Has anyone with suggestion? Greatly appreciate!

      • Catalin
        June 20, 2013 - 7:28 pm Reply

        Hi, first i want to thank to the author for this code.
        @ Sam Wong: Hello Sam in not an expert in programming but i have in ideea. You can creat an exe (or atctivex) with a flag for creation of txt file. You call the exe with parameters and wait for the flag to become true. After that you go forward with zipping method.

  • Sanjay
    November 12, 2012 - 8:29 pm Reply

    Excellent program. Worked great on zipping my access database.

    • Juan Soto
      November 14, 2012 - 10:43 pm Reply

      Your welcome!

  • André Luiz Bernardes
    August 23, 2012 - 2:05 am Reply

    Hello Ben Clothier, your solution is pretty cool.

    I would translate it to Portuguese in my blog.

    And make the necessary referrals.

    André Luiz Bernardes
    bernardess@gmail.com

    • Juan Soto
      August 25, 2012 - 6:00 pm Reply

      Thanks Andre!

      Send us the link!

      Juan

  • Steve Robinson
    August 8, 2012 - 10:31 pm Reply

    I love the code but I need to deal with a password protected zip file. How would I pass a parameter like a password? The file comes from a production system that has a well documented process that will not change for a squirrel like me…

    • Juan Soto
      August 9, 2012 - 3:13 am Reply

      Sorry Steve, passwords are not supported.

  • Vicki
    July 20, 2012 - 2:14 am Reply

    Hi Ben,

    Thank you for writing this code; I understand that it’s based on Ron de Bruin’s code, but I do find your explanations quite helpful.

    Though I’m getting to be passable at scripting within Access and Excel, I am very new to trying to automate anything using Windows automation.

    I’m very interested in this, but I’m stumped. I’ve tried your code, plus several variations I’ve found around the ‘net, and no matter how I approach it, my system seems to hate “NameSpace” with .zip files. It works fine on regular folders. But, if I try to use it with zip files, my process errors out every time.

    For example, when I use the code you’ve provided here (verbatim – no changes), it errors when it gets to this line:
    Set oFld = oApp.Namespace(CVar(ZipFile))

    The error is:
    The system cannot find the file specified

    Alternately, if I try to just copy the Excel file into the zip file, I get:
    Method “NameSpace” of object ‘IShellDispatch4’ failed

    I’m using Access 2007 on Windows XP SP3. Do you know of any issues with this? And, if yes, is there a work-around? I have to zip 60+ files on a regular basis, and would love to be able to automate it.

    Thanks for any help you can give!

    • Ben Clothier
      July 20, 2012 - 8:02 pm Reply

      Just to be 100% sure, did you check what the variable “ZipFile” contains? Does it have the path correct and includes “.zip” at the end?

      I’ve tested the code on XP & Win 7 so I am not aware of any special issues that may block the procedure from working correctly.

  • Javier
    June 14, 2012 - 1:41 am Reply

    Excellent job, I’ve started using the script for one of my automated exports. I’m using the zipping multiple file feature. One question, how do I suppress the Confirm File Replace dialog if I have to rerun the script to post a daily file?

    • Ben Clothier
      June 14, 2012 - 1:47 am Reply

      Thanks for writing back, Javier. I’m glad you found it useful. As for the question, you would have to either delete or rename the original file. The dialog only comes up when the file you’re trying to unzip would end up in a folder that already has a file of same name. Hope that helps.

  • Stefan
    February 10, 2012 - 11:04 pm Reply

    Hello,

    thx a lot for your great job. Is there any way not have to wait until unzipping has finished but to initiate unzipping and go further on while unzipping?
    thx for your answer
    kind regards

    • Ben Clothier
      February 10, 2012 - 11:46 pm Reply

      Stefan — as long you do not try to interact with files right after unzipping, you could add a boolean flag as a parameter to the function… call it NoWait and wrap the part where we wait in a If NoWait = False Then … End If block. Just keep in mind that trying to access file before they’re completely unzipped may result in an error.

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
[gravityform id="16" title="false" description="false"]