Zip Activeworkbook, Folder, File or Files with 7-Zip (VBA)

Hi all

I upload a new page today with code to create Zip files with the free Zip program 7-zip.
This program is also very useful if you want to open Office 2007 files as a archive
without changing the extension to .zip so you can see/edit the files in the archive.

Zip Activeworkbook, Folder, File or Files with 7-Zip (VBA)
http://www.rondebruin.nl/7zipwithexcel.htm

I will add a Unzip page to my site for 7-zip it next week

For examples for WinZip or the default Windows Zip program see the Zip (compress) section here
http://www.rondebruin.nl/tips.htm

Have fun and let me know if you have suggestions or find a bug

Posted in Uncategorized

9 thoughts on “Zip Activeworkbook, Folder, File or Files with 7-Zip (VBA)

  1. Ron,
    7zip is great. I started using it when Windows default zip wouldn’t handle different types of encryption and I find it much better than anything else. Winzip is dead to me.

  2. Agree with comments about 7-zip. I’ve used WinZip with the optional extra install for command-line access, our standard at work is ZipCentral which is o.k. but has no command-line interface and isn’t being developed any further. Read about 7-zip in a magazine and haven’t looked back. Some of the options vary depending on whether you’re creating a .zip or the native 7-zip archive format so there’s some trial and error needed.

  3. Hi

    Good to read that I ma not the only one that like the program

    FYI

    I add the page with a few basic unzip examples
    Link is on the Zip page

    Have fun

  4. Hi Ron, I’ve benefited from much code that you have written and I’m wondering if a small tweak is possible to what you’ve written here: http://www.rondebruin.nl/windowsxpzip.htm

    I’m using the code effectively but what I would prefer to do is NOT use FName = Application.GetOpenFilename as the means to get the file(s).

    Instead I have a spreadsheet with the list of files I want to zip in column A. My preference is something like:

    FName = Activecell.Value

    or maybe something like:

    Sub DoSomethingToSomeCells()
    Dim FName As Range
    For Each FName In Selection
    ‘Zip the FName
    Next FName
    End Sub

    Is this possible?…JD

  5. Hi, Try this untested change for the files in range
    Sheets(“Sheet1?).Range(“A1:A2?)

    You can test if the files exst also when you fill the array if you want

    Sub Zip_File_Or_Files()
        Dim strDate As String, DefPath As String, sFName As String
        Dim oApp As Object, iCtr As Long, I As Long
        Dim FName(), vArr, FileNameZip
        Dim cell As Range

        DefPath = Application.DefaultFilePath
        If Right(DefPath, 1) <> “” Then
            DefPath = DefPath & “”
        End If

        strDate = Format(Now, ” dd-mmm-yy h-mm-ss”)
        FileNameZip = DefPath & “MyFilesZip “ & strDate & “.zip”

        Fnum = 0
        For Each cell In Sheets(“Sheet1”).Range(“A1:A3”)
        Fnum = Fnum + 1
            ReDim Preserve FName(1 To Fnum)
            FName(Fnum) = cell.Value
        Next cell

        If Fnum > 0 Then
            ‘Create empty Zip File
           NewZip (FileNameZip)
            Set oApp = CreateObject(“Shell.Application”)
            I = 0
            For iCtr = LBound(FName) To UBound(FName)
                    ‘Copy the file to the compressed folder
                   I = I + 1
                    oApp.Namespace(FileNameZip).CopyHere FName(iCtr)

                    ‘Keep script waiting until Compressing is done
                   On Error Resume Next
                    Do Until oApp.Namespace(FileNameZip).items.Count = I
                        Application.Wait (Now + TimeValue(“0:00:01”))
                    Loop
                    On Error GoTo 0
            Next iCtr

            MsgBox “You find the zipfile here: “ & FileNameZip
        End If
    End Sub

  6. Thank you!

    I did have to make a minor change:

    oApp.Namespace(FileNameZip).CopyHere DefPath & FName(iCtr)

    This will save me a lot of time. I really appreciate you help and how generous you are with your time and expertise…JD

  7. Thanks Ron, I checked the code but its showing error for “NewZip (FileNameZip)”. could you please help?

  8. IS there a way to zip bunch of txt files created thru VBA? I need to zip them and password protect it.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.