Hello everyone,

I have a VB code for finding all the files in a given directory as given below:


[VBA]Sub SearchFiles()

Dim i As Long, z As Long, Rw As Long
Dim ws As Worksheet
Dim y As Variant
Dim fLdr As String, Fil As String, FPath As String

y = "*"
If y = False And Not TypeName(y) = "String" Then Exit Sub

fLdr = strStartPath

With Application.FileSearch
.NewSearch
.LookIn = fLdr
.SearchSubFolders = True
.Filename = y
Set ws = ThisWorkbook.Worksheets.Add(Sheets(1))
On Error GoTo 1

2: ws.Name = "File Search Results"
On Error GoTo 0
If .Execute() > 0 Then

filefound = 1

For i = 1 To .FoundFiles.Count
Fil = .FoundFiles(i)

FPath = Left(Fil, Len(Fil) - Len(Split(Fil, "\")(UBound(Split(Fil, "\")))) - 1)
If Left$(Fil, 1) = Left$(fLdr, 1) Then
If CBool(Len(Dir(Fil))) Then
z = z + 1
ws.Cells(z + 1, 1).Resize(, 4) = _
Array(Dir(Fil), _
FileLen(Fil) / 1000, _
FileDateTime(Fil), _
FPath)
ws.Hyperlinks.Add Anchor:=Cells(z + 1, 1), _
Address:=.FoundFiles(i)
End If
End If
Next i
End If
End With

ActiveWindow.DisplayHeadings = True

If filefound = 1 Then

With ws
Rw = .Cells.Rows.Count
With .[A1: D1]
.Value = [{"File Name","File Size (KB)","Last Modified", "Path"}]
.Font.Underline = xlUnderlineStyleSingle
.Font.Bold = True
.EntireColumn.AutoFit
.HorizontalAlignment = xlCenter
End With
'.[E1:IV1 ].EntireColumn.Hidden = True
On Error Resume Next
'Range(Cells(Rw, "A").End(3)(2), Cells(Rw, "A")).EntireRow.Hidden = True
Range(.[A2 ], Cells(Rw, "C")).Sort [A2 ], xlAscending, Header:=xlNo
End With

Else 'If no file found

Msg = "No files were found. Please change the specifications."
Title = "No Files Found"
Response = MsgBox(Msg, vbOKOnly, Title)

If Response = 1 Then
End if

End If

Application.ScreenUpdating = True

Exit Sub

1: Application.DisplayAlerts = False
Sheets("File Search Results").Delete
Application.DisplayAlerts = True
GoTo 2

End Sub



[/VBA]

But, this code does not detect the .zip files and the search results do not dislpay the zip files kept in the directory.

Can you suggest changes in the above code (preferably without using 'File System Object') which would allow it to treat zip files as 'files' and not 'folders'?

Regards,
ASA