Zip files with vba

Contrary to the copying to a zip folder above , this code waits for the copying to finish, and will not continue until copying is done. Armed with these two functions, you can control all basic zipping and unzipping from VBA. No external tools or libraries are used, which makes distributing and deployment easy.

Of course, more advanced options, like encrypting, may be requested, and then third-party tools like WinZip or 7Zip must be used. But this will be more about zip files while we here discuss zip folders as we know them from Windows. The complete module and an Access file is here: Compress 1. I hope you found this article useful.

You are encouraged to ask questions, report any bugs or make any other comments about it below. Note : If you need further "Support" about this topic, please consider using the Ask a Question feature of Experts Exchange. I monitor questions asked and would be pleased to provide any additional support required in questions asked in this manner, along with other EE experts.

Please do not forget to press the "Thumbs Up" button if you think this article was helpful and valuable for EE members. Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Start Free Trial. Log In. Web Dev. Experts with Gold status have received one of our highest-level Expert Awards, which recognize experts for their valuable contributions. Published: Updated: Edited by: Andrew Leniart. Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc.

In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here. Ease of use There are many uses for zip folders. If you are interested in this, please see: Handle cabinet files and folders with VBA the Windows Explorer way Zip and Unzip These two functions are included in the code and does just that in a way that - by default - matches the result as if you did it manually onscreen using Windows Explorer.

Code Both Zip and UnZip takes advantage of two standard Microsoft libraries found on any Windows machine: Microsoft Shell Controls And Automation Microsoft Scripting Runtime and both functions allow for early or late binding as you prefer, controlled by the constant EarlyBinding that you can adjust to your preference. If so, the current path is used.

Gustav Brock. Application" End If ' Mandatory extension of zip file. FileExists Path Then ' The source is an existing file. GetFile Path. FolderExists Path Then ' The source is an existing folder. GetFolder Path. ParentFolder Else ' The source does not exist. Write ZipHeader. Close End With ' Resolve relative paths. With ShellApplication Debug. It is not possible to hide the copy dialog when you copy to a zip folder this is only working with normal folders as far as I know.

Also there is no possibility to avoid that someone can cancel the CopyHere operation or that your VBA code will be notified that the operation has been cancelled. This must be a Variant , if you change this the code will not work. If you want to Unzip files see this page on my site. Unzip file s with the default Windows zip program VBA.

There are five examples on this page that you can copy in a normal module of your workbook. Please read the information good above before you start testing the code below. Browse to the folder you want and select the file or files. Change this folder or use your default path Application. Step 2 — Exploration takes place inside the While…Wend loop on lines 23 through In a nutshell, the purpose of this function is to return the name of the zip file.

Easy cheesy! Lines wrap up the Step 2 — Exploration portion of our script, and all four of these lines revolve around creating the String that we will launch from the command line. If we were to Debug. Print our strCommand variable for the first run in this sheet, it would look like this:. Step 3 — Execution takes place from line 39 to line 45, and it all revolves around the WshShell object, which is stored as wsh in our script. On line 39, we assign lngErrorCode which will be a number to the output of wsh.