View Full Version : Getting filenames of Zipped Folders

09-21-2005, 11:57 AM
Excel will not find any .zip files and search for the name in variable "kerrymatch".

If I rename the files to .txt in the subfolder, it finds them.

Any ideas?


Do While master.Cells(masterRow, 3) <> ""
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '
With Application.FileSearch
.LookIn = "S:\Kerry\packages\"
.SearchSubFolders = True
.Filename = kerrymatch
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles

If .Execute() <> 0 Then
master.Cells(masterRow, 4).Select
Selection.Interior.ColorIndex = 6
Selection.Pattern = xlSolid
MsgBox ("FOUND")

masterRow = masterRow + 1
kerrymatch = master.Cells(masterRow, 3)

master.Cells(masterRow, 4) = "Not Found"

masterRow = masterRow + 1
kerrymatch = master.Cells(masterRow, 3)
End If
End With

09-21-2005, 12:32 PM
Hmmm... it doesn't work does it?
Maybe we're missing something obvious but I suspect this might because since M$ hijacked the ZIP format, the whole world has to regard them as "compressed folders" rather than files - XP certainly does and Excel's Filesearch seems to be playing along.
Luckily, M$'s 3rd law of inconsistency means that the DIR command and the FileSystemObject wouldn't know a "compressed folder" if one extracted itself from inside their abdomen and burst out of their chest, so you can use one of them.
Here's a FileSystemObject exampleDim fso, fldr, f

Set fso = CreateObject("Scripting.FileSystemObject")
Set fldr = fso.GetFolder("C:\Documents and Settings\Killian\Desktop")
For Each f In fldr.Files
If Right(f.Name, 4) = ".zip" Then
MsgBox "Yay! A zip FILE!"
End If

09-21-2005, 03:16 PM
Hi and Welcome to VBAX! :hi:

Or use the VBA Dir function to do this:
Sub FindZip()
Dim sFile As String
Dim sPath As String
sPath = "C:\Documents and Settings\Joost Verdaasdonk\Bureaublad\"
sFile = Dir(sPath & "*.zip", vbNormal)

Do Until sFile = ""
MsgBox "Yeah..:" & vbCr & sPath & sFile & vbCr & _
"Is a Zip File", vbExclamation, "Zip Files"

sFile = Dir()

End Sub

HTH, :whistle:

09-21-2005, 03:39 PM
Oh yes, first post and I forgot to welcome you :o:

Welcome! :wavey: Hope you're enjoying it so far...

10-19-2005, 07:41 PM
Hey guys - thanks for the responses.

I found that if you enter these two lines before the routine Zip files are located with no problem:

result = Shell("regsvr32 /u cabview.dll /s", vbHide)
result = Shell("regsvr32 /u zipfldr.dll /s", vbHide)