Use VBA to zip individual files in a folder to individual zip files

HI,

I am trying to find a piece of VBA code that would allow me to zip individual files in a folder to individual zip files.  A one to one relationship if you will.  So if I have 10 files in a folder, each file would be zipped into its own zip file thus creating ten zip files with one file in each.  Thanks for your help!

BTW...I am using Office 2007.  I do have access to Office 2003/XP as well.  I am running on XP pro.

 

Question Info


Last updated March 3, 2020 Views 18,751 Applies to:

VBA doesn't have any native support for zip files. For simple zip tasks, I use the XZip ActiveX component fromhttp://www.xstandard.com/en/documentation/xzip/ . Download the zip file into the folder of your choice and unzip it. Then, go to the Windows Start menu, choose Run, and enter

RegSvr32 <path>\XZip.dll

where <path> is the complete folder name of the folder to which you unzipped the downloaded file. Once you have successfully registred the file with Windows via the RegSvr32 program, open Excel and then the VBA editor. In the VBA editor, go to the Tools menu, choose References, and check the entry for "XStandard - Zip 2.5" .  With that reference in place, it is very easy to zip files or folders.

The following code will zip each individual file in C:\Test to a zip file with the same name in C:\Test2. Change the lines of code marked with '<<<< to your desired directories. It is perfectly valid for both FromFolderName and ToFolderName to refer to the same directory.

Sub AAA()
    Dim Z As XZip.Zip
    Dim FromFolderName As String
    Dim ToFolderName As String
    Dim FileName As String
    Dim N As Long
   
    Set Z = New XZip.Zip
    FromFolderName = "C:\Test" '<<<< CHANGE
    ToFolderName = "C:\Test2" '<<<< CHANGE
   
    FileName = Dir(FromFolderName & "\*.*", vbNormal)
    Do Until FileName = vbNullString
        N = InStrRev(FileName, ".")
        Z.Pack FromFolderName & "\" & FileName, _
                ToFolderName & "\" & Left(FileName, N) & "zip"
        FileName = Dir()
    Loop
End Sub

 

 


Cordially, Chip Pearson Microsoft MVP, Excel Pearson Software Consulting, LLC www.cpearson.com
Cordially,
Chip Pearson
Excel MVP 1998 - 2014
Pearson Software Consulting, LLC
www.cpearson.com

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.


7zip is free and can be started from the command line.  http://www.7-zip.org/

If source and destination folders are constant, you might want to try the first version of my code. The second version gives you the option to choose. Please adjust the Const-statements in the code.

Peter

Sub ZipIndividualFiles1()
   Dim file As Variant

   Const SOURCE     = "E:\test\source"
   Const DEST       = "E:\test\destination"
   Const PATH_TO_7Z = "C:\Program Files\7-Zip\7z.exe"

   For Each file In CreateObject("Scripting.FileSystemObject").GetFolder(SOURCE).Files
      Shell PATH_TO_7Z & " a -tzip """ & DEST & "\" & file.Name & ".zip"" """ & file.Path & """"
   Next
End Sub


Sub ZipIndividualFiles2()
   Dim src  As Variant
   Dim dst  As Variant
   Dim file As Variant

   Const PATH_TO_7Z = "C:\Program Files\7-Zip\7z.exe"

   Set src = CreateObject("Shell.Application").BrowseForFolder(0, "Source folder", &H245)
   If Not src Is Nothing Then
      src = src.Self.Path & "\"

      Set dst = CreateObject("Shell.Application").BrowseForFolder(0, "Destination folder", &H245)
      If Not dst Is Nothing Then
         dst = dst.Self.Path & "\"

         For Each file In CreateObject("Scripting.FileSystemObject").GetFolder(src).Files
            Shell PATH_TO_7Z & " a -tzip """ & dst & file.Name & ".zip"" """ & file.Path & """"
         Next
      End If
   End If
End Sub

7 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

See also the Zip section "Zip(compress)....." on my site
See link below



Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
  "Spartan183" wrote in message news:*** Email address is removed for privacy ***...

HI,

I am trying to find a piece of VBA code that would allow me to zip individual files in a folder to individual zip files.  A one to one relationship if you will.  So if I have 10 files in a folder, each file would be zipped into its own zip file thus creating ten zip files with one file in each.  Thanks for your help!

BTW...I am using Office 2007.  I do have access to Office 2003/XP as well.  I am running on XP pro.

Regards Ron de Bruin
http://www.rondebruin.nl

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thanks so much for all of your responses.  Let me take a look at them.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hey Peter I was just wondering how would you use your first chunk of code for just an individual .exe file? I works for me for folders but I do not want to zip the entire folder.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

you can find a example here

http://www.rondebruin.nl/7zipwithexcel.htm

 

Regards Ron de Bruin
http://www.rondebruin.nl

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi Ron do you know of a way to get word 2007 using VBA to zip a whole folder including sub folders and files?

 

The location of the folder I need to zip is "C:\[ SKYDRIVES ]\SkyDrive\H&S Files\"

 

I have tried using the code you have got on your website but think I must be doing something wrong :(

 

Thanks in advance for your help.

 

Regards

 

Phill

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thank you for sharing this code!

But when i'm trying to applied it in my code, it doesn't work, it just opened the 7-zip application, and didn't zip the file. Can you help me? My file is pdf format and i'm using 7-zipportable.

Does it affect the code?

Thanks

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.