Zip file using vba excel

Using VBA to zip files. Thread starter DGelinas Start date Aug 9, DGelinas New Member. Joined Aug 9, Messages 3. Good day, I am looking for a way to zip all of the files in a folder automatically.

I found the following from Ron de Bruin, which is almost exactly what I need: Zip file s with the default Windows zip program VBA The section "Browse to a folder and zip all files in it" leads to exactly the end result I want, except that I do not want to browse to the folder.

I would then like the macro to go to each folder, and create Files Instinctively, I feel this should only require changing the code so that it chooses the folders based on the contents of the file, instead of by browsing, but I have no idea how to do that or even if it is possible.

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. Using Function Arguments with nested formulas. Click here to reveal answer. 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. 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. Run with three inputs:. If the command was successful, lngErrorCode will be 0 zero. We check explicitly from line for a successful run, and Exit Sub if something goes wrong.

The purpose of this code is to ask for folder with zipped files and then extract all ZIP files into the existing path e. Namespace UnzipedDirPath. CopyHere oApp.

Namespace ZipPath. Can anyone help me please? I spent few hours solving why the hell I get the Run-time 91 error. Thanks a lot! Hi Excel Off The Grid, points 1 and 2 are actually the way you proposed in my code. It has just been copied here in some strange way.

I certainly have marks in the correct place as you proposed. I also tried to convert the type of variables by CVar like this: oApp. Namespace CVar ZipPath.

After making the changes I noted above I was able to get files to unzip — I will send you the exact code. Namespace unzipToPath. CopyHere shellApp. Namespace zippedFileFullName. If you define the strPath as a Variant rather than a String, it should work.

Replace this: Dim strPath As String. Thanks a lot.. Application views it as a folder object rather than a String, which is why it needs to be a Variant. By passing a String between the procedures it remains as a String, therefore it is necessary to create it as a Variant initially.

Want small help from vba experts, have data with multiple clients mapped to employee in Excel which later I am saving in. PDF file format of every client mapped on the basis of employee for I. Thanks for the question. I will send you an e-mail. Hopefully I can get a few more details and help you out. Hi Ashwin — as this is not a support forum, I can only help readers with specific problems if and when I have time. I have received your file, but not had chance to look at it yet.

I hope to look at it later today. As it appears you want a faster resolution than I can provide, I suggest you try the Mr Excel forum. Namespace in the last line of code should be changed to ShellApp. I have coding that will count the files in the folder. I have trawled the internet and have several problems with any and all codes that I have found.

Managed to get this to work. Thanks Mark. Hi , All, I have a query. Application" 'Copy the files to the compressed folder oApp. Namespace FileNameZip. My issue is while zipping the folder zipping with out any contants. If I tried oApp. Namespace FolderName. Namespace FolderName all the files will be zipped. But my requirement is if i unzipped the folder with the option extract here, folder should be extracted with its contents in it.

The unzipping code is working fine for me. But i have another issue that after unzipping i am moving the file to a certain folder. I am using the below syntax for this unzipping and file moving ShellApp.

CopyHere ShellApp. Before unzipping i don t know the file name so unable to check that file already exists or not in the target folder. I have used Application. Please help me here. Thanks in advance. Thanks for this code.

NameSpace zippedFileFullName. NameSpace unzipToPath. Dim objAtt As Outlook. The VBA code calls the standard Windows zip and unzip procedure. I would really appreciate if you could help me to debug it. Namespace folderToZipPath. It contains one excel file to be zipped. My code is almost as in the article above, I just specified the path and the file name:. Count Application. The issue is because the zippedFileFullName needs to refer to a valid file path, then the file name.

Hi is there a way of doing a loop to unzip all the files in a folder and move them to another folder using excel vba. I was looking to do this using a batch file but could not see anything for windows. I would recommend looping through the files in the folder first, creating an array of the zip files. Then looping through the array of zip files to carry out the actions on each zip. Hello, Thanks for the ideas on better use of VBA. These hints are greatly appreciated.

I think the unzipping method will be a big productivity enhancement for data retrieval from zip archive and parsing. I worked through the bugs and have a working prototype for use tomorrow. I will migrate this capability as I go forward.