ASA
11-10-2008, 04:42 AM
Hello everyone,
I have a VB code for finding all the files in a given directory as given below:
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
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
I have a VB code for finding all the files in a given directory as given below:
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
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