Zip vba

My perfect end result would be a macro that would work in such a way that I only need to change the folder names in the future to zip other folders; this would speed up year end archiving, among other things.

Any thoughts, wise people? Thank you in advance! Excel Facts. Shade all formula cells. Click here to reveal answer. Then apply a light fill color. James Well-known Member. Joined Apr 4, Messages 3, Hi, Ron's code is by far the best solution What you are requesting is obviously possible with a minor modification within the code James said:.

Click to expand Hi, Below is a possibility Application" 'Copy the files to the compressed folder oApp. Namespace FileNameZip. Use Excel Rescue , who are my consultancy partner. They help by providing solutions to smaller Excel problems. Rename columns in Power Query when names change.

Clear old items from a PivotTable filter list. Why are Excel formulas not calculating? VBA to sort sheets alphabetically.

Office Scripts — Sort sheets alphabetically. Use Office Scripts with Power Automate. Office Scripts — Hide all sheets except one. August 22, at pm. Excel Off The Grid says: I should also say that whilst the code snippets above will create the Zip file, all the folder paths must already exist. Resulter says: I am creating them in my code before this action. They really exist Reply. March 19, at am. December 12, at pm. March 17, at pm.

March 18, at pm. This makes me think it is not an issue with the VBA code, but an alternative issue. March 19, at pm. Yasser says: Thanks a lot.. Yasser says: Thank you very much for great assistance Reply. Ashwin says: Want small help from vba experts, have data with multiple clients mapped to employee in Excel which later I am saving in. October 8, at pm.

Ashwin says: Sample Details shared Reply. October 9, at am. Ashwin says: Pls reply Reply. October 10, at am. Excel Off The Grid says: Hi Ashwin — as this is not a support forum, I can only help readers with specific problems if and when I have time.

Abou says: Dear All Hope you are fiine. I have an issue with my vba code. Would it be possible to help. January 15, at pm. Excel Off The Grid says: Hi Abou, From looking at your code there appear to be a few of issues: 1 fpath and fpath2 need to be created a Variant variables, rather than String variables. Namespace Then I think it should work. January 16, at am. Andrew says: This code helped me a lot! January 31, at pm. Rick says: hi all, apologies for my ignorance but I just cant get this to work.

April 3, at pm. Rick says: Managed to get this to work. Mark Reply. Rick says: Thanks Mark. Subi says: Hi , All, I have a query. Thanks Reply. May 23, at am. Dan Harrier says: I also had the Error May 29, at pm. June 4, at pm. Srimanta says: Hi All, The unzipping code is working fine for me. Srimanta Reply. August 13, at pm. August 31, at pm. Ben Houghton says: Thanks for this code. 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. Zip and mail the ActiveWorkbook This will only work if you use Outlook as your mail program. This sub will send a newly created workbook copy of the Activeworkbook. After the zip file is sent the zip file and the workbook will be deleted from your hard disk.

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.

They can do a little more, controlled by the parameters as explained below, and will also work with custom file extensions, should you have the need. Thus, a zip folder may be named, say, myzipfolder. This will prevent the file from being recognized by Windows Explorer as a zip folder.

Result holds 0 zero if success, or an error code. If you don't want this, a third parameter is available, Overwrite. Thus, if the destination exists:.

This unzips Path a zip folder as if you, in Windows Explorer, right-click the zip folder and select Unzip all If so, specify the Destination parameter:. By default, if unzipping to an existing folder, UnZip will leave existing other files, but overwrite those named as the files in the zip folder. Setting parameter Overwrite to True, will overwrite the existing folder completely:.

Be careful and double-check before calling UnZip with Overwrite set to True. If Destination is specified incorrectly, you may inadvertently erase most of a drive.

Both Zip and UnZip takes advantage of two standard Microsoft libraries found on any Windows machine:. There are many more details to take care of than one might expect, thus a lot of comments are outlined here to study for those that are interested. But don't feel obliged; the functions are ready to use as is.

A problem here is, however, that after calling this, our code would just continue, while - as you know - zipping a lot of files can take some time, and we have some clean-up to do afterwards. Thus, it is necessary to put in a loop that regularly checks if the zip folder is ready and, only when it is, to continue. This is shown here, where the Sleep function in the Do Until Loop inserts a tiny delay which will be repeated over and over until the zip folder is ready:. In case parameter Overwrite is False and the zip folder exists, a new file will be created and given a version number.

Again, the in-line comments should make it possible to follow its doings, but in essence it performs these steps:. Again, the core code is quite simple; it copies the content of the source - the zip folder - into the destination folder:. 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.