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).

MS Access Consulting

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).