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

[Instantaneo]

Hi,


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"))
        Loop
        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!