How to extract filenames from multiple zip/compressed folders

[Arif41]

I have folder named as "Task" which contains 10+ zip folders and each zip folder contain minimum 20 files.
Now, in an excel I want to extract all the filenames in column A and their respective zip folder name in column B

Can someone help with macro/VBA which can be used to obtain this data

 

[mjbeam]

You access the names and paths of files in a zip file with this code:

    Set os1 = CreateObject("Shell.Application")

    Set s1 = os1.Namespace("C:\Your_zip_file_here.zip").Items
    For Each t1 In s1
        zipName = t1.Name
        zipPath = t1.Path
    Next

 

[mjbeam]

Here is some code that will loop through all files in the Task folder, and extract the names and paths of all files found in the zip files.

    Set os = CreateObject("Shell.Application")
    
    Set filesys = CreateObject("Scripting.FileSystemObject")
    
    Set thisFolder = filesys.getFolder("C:\...whatever...\Task")
    
    destRow = 1
    For Each thisZipFile In thisFolder.Files
        Set thisOS = os.Namespace(LCase(thisZipFile.Path)).items
        For Each thisZippedFile In thisOS
            
            Cells(destRow, 1) = thisZippedFile.Name
            Cells(destRow, 2) = thisZippedFile.Path
            destRow = destRow + 1
        Next
    Next

 

[Arif41]

I have the below coding.... I am only able to extract filenames from 1 zipfolder at a time. Is there any way where i can modify this to obtain the entire list at once.

Sub ListZipDetails()
Dim R As Long, PathFilename As Variant, FileNameInZip As Variant, oApp As Object
PathFilename = Application.GetOpenFilename("ZipFiles (*.zip), *.zip")
If PathFilename = "False" Then Exit Sub
R = Cells(Rows.Count, "A").End(xlUp).Row
Set oApp = CreateObject("Shell.Application")
For Each FileNameInZip In oApp.Namespace(PathFilename).Items
R = R + 1
Cells(R, "A").Value = FileNameInZip & " (" & PathFilename & ")"
Next
Set oApp = Nothing
End Sub

 

[Arif41]

Thank you mjbeam for the above code.

However, using this i am please able to get zip file name in column A & the zipfile path in column B.

I am trying to obtain the filenames (which are in side each of the zip files) in column A and the zip folder name in column B

can you please see if this is possible

 

[mjbeam]

The code I provided does what you asked. If it is not working for you can you describe what the problem is?

 

[mjbeam]

If you really need to manually select the zip files you want to look into you can do that like this:

Sub ListZipDetails()

    Dim R As Long, PathFilename As Variant, FileNameInZip As Variant, oApp As Object
    
    tPathFileName = Application.GetOpenFilename("ZipFiles (*.zip), *.zip", , , , True)
    If UBound(tPathFileName, 1) = 0 Then Exit Sub
    
    For Each PathFilename In tPathFileName
        R = Cells(Rows.Count, "A").End(xlUp).Row
        Set oApp = CreateObject("Shell.Application")
        For Each FileNameInZip In oApp.Namespace(PathFilename).Items
            R = R + 1
            Cells(R, "A").Value = FileNameInZip & " (" & PathFilename & ")"
            Cells(R, "B").Value = FileNameInZip.Path
        Next
    Next
    
    Set oApp = Nothing

End Sub

This will allow you to select multiple ZIP files by holding down ctrl or shift while you select them.