Excel VBA to ZIP a set of files (with some different aspects)


New Member
Dec 5, 2012

I'm creating a macro that asks the user a certain folder as input and then it zips to a single file a set of other files that are in different folders (that depend on the first one).

I've adapted the code that is referred in different forums (from Ron de Bruin - I don't know if I can put it here) but I'm failing on creating some particularities.

So far, here it is the code I have:

Sub NewZip(sPath)
'Create empty Zip File
'Changed by keepITcool Dec-12-2005
    If Len(Dir(sPath)) > 0 Then Kill sPath
    Open sPath For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
End Sub

Sub Zip_Files()
    Dim strDate As String, folderbasis As String, sFName As String
    Dim oApp As Object, I As Integer
    Dim FileNameZip

    folderbasis = ThisWorkbook.Worksheets("ControlPanel").Range("D5")
    If Right(folderbasis , 1) <> "" Then
        folderbasis = folderbasis & ""
    End If
    strDate = Format(Now, "yyyymmdd_hhnnss")
    FileNameZip = folderbasis & strDate & "_Zip" & ".zip"
    'unique files
    file_tool = folderbasis & "tool.xlsm"
    file_kpi_node = folderbasis & "Tool\KPI\output\NodeKPIs.csv"
    file_kpi_link = folderbasis & "Tool\KPIoutput\LinkKPIs.csv"
    file_kpi_area = folderbasis & "Tool\KPI\output\AreaKPIs.csv"
    file_kpi_od = folderbasis & "Tool\KPI\output\Area-AreaKPIs.csv"
    file_map = folderbasis & "map\map.osm"
    file_zones = folderbasis & "map\zones.sql"
    file_centroids = folderbasis & "map\centroids.sql"
    file_days = folderbasis & "sql\day.sql"
    file_segs = folderbasis & "sql\mode.sql"
    file_CL_KPI = folderbasis & "Tool\KPI\CommandLineTDE.csv"
    file_CL_MP = folderbasis & "Tool\MP\CommandLineTDE.csv"
    'multiple files
'    file_trajectories = folderbasis & "Tool\trajectories\*.csv"
'    file_log_KPI = folderbasis & "Tool\KPI\LogFile_DataDrivenModel.log*"
'    file_log_KPI = folderbasis & "Tool\MP\LogFile_VehicleTracker.log*"
    NewZip (FileNameZip)
    Set oApp = CreateObject("Shell.Application")
        'Copy the file to the compressed folder
            oApp.Namespace(FileNameZip).CopyHere file_tool
            oApp.Namespace(FileNameZip).CopyHere file_kpi_node
            oApp.Namespace(FileNameZip).CopyHere file_kpi_link
            oApp.Namespace(FileNameZip).CopyHere file_kpi_area
            oApp.Namespace(FileNameZip).CopyHere file_kpi_od
'            oApp.Namespace(FileNameZip).CopyHere file_map
'            oApp.Namespace(FileNameZip).CopyHere file_zones
'            oApp.Namespace(FileNameZip).CopyHere file_centroids
'            oApp.Namespace(FileNameZip).CopyHere file_days
'            oApp.Namespace(FileNameZip).CopyHere file_segs
'            oApp.Namespace(FileNameZip).CopyHere file_CL_KPI
'            oApp.Namespace(FileNameZip).CopyHere file_CL_MP
        '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"))
        On Error GoTo 0
        MsgBox "The file is completely zipped!"
        Set oApp = Nothing
End Sub

At this moment, I can create the zip file of some of the files stated above. But this has several problems or things I can't solve:

1. From the list of those 12 files, I'm testing it only with 5 (the ones with the '). However, sometimes it only compresses 1, 2, 3 or 4, getting the remaining number of error messages saying "File not found or no read permission!". It quite random the files that are zipped or not...
2. I want the MsgBox in the end to appear only when the zip is complete, but apparently it shows up as soon as a "temporary" zip is created. Is there any way to change this?
3. As you can see, besides from that list of 12 files that have always the same name and the same location, I have three lines (currently disabled) where I pretend to zip a trajectories file that can have any name (*.csv) and log files from two folders that can have any name and any number (LogFile1.log, LogFile2.log, ..., LogFile50.log, ...). How can I get routines to say that I should zip the file with the unknown name of the first folder, and all the files from the other folders that match that name rule?
4. Some of the files may not exist. Do I need to put an If in every file prior to the "CopyHere" section?

Thanks in advance for your help!

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Watch MrExcel Video

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back