Selecting files using macro and converting it to zip file

[soorajjames]

Hello all,

I am a beginner in macros. Here is what i wish to do. From a given cells in a column i want to select a particular pdf file and combine all those files into a zip file. For eg if the excel sheet contains the following columns: Roll number, Preference. So if i have the following data under these headers:

1000 Engineering
1002 Management

I need to search the folder named 1000 and 1002 and find engineering and management pdf files and combine the two into a zip file. Can i do this suing macros. Can anyone please guide me.

Thank you.

 

[Domenic]

My understanding is the following...

1) the first row contains the column headers

2) the data starts in the second row

3) Column A contains the name of the folder (ie. 1000) in which the desired PDF file is found

4) Column B contains the text (ie. Management) contained in the PDF filename

If I understood correctly, try the following macro that needs to be placed in a regular module (Visual Basic Editor (Alt+F11) >> Insert >> Module). Note that there's no error handling. So it assumes that the sheet containing the data is the active sheet, and that the specified path to the folders exists. Here's the code (make the necessary changes, where indicated)...

Option Explicit

Sub Zip_PDF_Files()


    Dim objShell As Object
    Dim objZipFolder As Object
    Dim varZipFileName As Variant
    Dim strPathToFolders As String
    Dim strFileName As String
    Dim fileCount As Long
    Dim lastRow As Long
    Dim i As Long
    Dim ans As Long
    
    'path and filename of zip file to compress files to (change accordingly)
    varZipFileName = "c:\users\domenic\desktop\compressed.zip"
    
    'create empty zip file with zip header
    Open varZipFileName 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] 
    
    Set objShell = CreateObject("Shell.Application")
    Set objZipFolder = objShell.Namespace(varZipFileName)
    
    strPathToFolders = "c:\users\domenic\desktop" 'must end in backslash (change accordingly)
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    'copy files to zipped folder
    fileCount = 0
    For i = 2 To lastRow
        strFileName = Dir(strPathToFolders & Cells(i, "a").Value & "\*" & Cells(i, "b").Value & "*.pdf", vbNormal)
        If Len(strFileName) > 0 Then
            fileCount = fileCount + 1
            objZipFolder.CopyHere strPathToFolders & Cells(i, "a").Value & "" & strFileName
            Do
                Application.Wait (Now() + TimeValue("00:00:03")) '3 second delay
            Loop Until objZipFolder.Items.Count = fileCount
        End If
    Next i
    
    If fileCount > 0 Then
        'display message box asking user whether to show zipped files in Windows Explorer
        ans = MsgBox(fileCount & " files zipped to:" & vbCrLf & vbCrLf & varZipFileName & vbCrLf & vbCrLf & "View zipped file?", vbQuestion + vbYesNo)
        If ans = vbYes Then
            Shell "Explorer.exe /e, " & varZipFileName, vbNormalFocus
        End If
    Else
        MsgBox "No files zipped!", vbExclamation
    End If
    
End Sub

Hope this helps!