Solved: Finding .zip files in directory

[ASA]

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

[RonMcK]

Asa,

Can you post the link for where you found this code? It might help us to have a clean copy of the code to help diagnose your problem.

[uvba]use vba[/uvba]

Thanks,

[Kenneth Hobs]

I always use Option Explicit. Based on it, I added some Dim's to your code. I also set an input parameter. Doing this, it worked fine for me.
[vba]Sub Test()
SearchFiles ThisWorkbook.Path
'Debug.Print Application.Path
'SearchFiles Application.Path
End Sub

Sub SearchFiles(strStartPath As String)

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

Dim filefound As Long
Dim Msg As Variant
Dim Title As String
Dim Response As Variant

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]

[ASA]

Mr. Kenneth Hobs,

The code is still unable to detect .zip files in the directory.

Technically, the " If .Execute() > 0 Then... End if " condition always becomes false even if zip file is present in the directory. The strange part is that the first time I used the code pasted by you, in a fresh workbook, it did detect the zip file. But in subsequent runs of code, it fails to detect zip files!

Could the possible reason be VBA References (Tools --> References)?

Please suggest an alternate solution to this.


RonMcK,

I did try to enclose my code in VBA tags, but strange as it was, it did not work!
So I had to edit it and paste it the way it is. Sorry for the inconvenience.
You can use the code enclosed by Mr. Hobs for your reference.


Regards,
ASA

[Kenneth Hobs]

If you can not use VBA tags, use CODE tags.

Where did you start your search from? Please post that code part. I don't see how a reference could change things.

I have had some problems with some search methods when searching from c:\. Using FileSearch with Excel 2007 won't work. For it, I prefer a DOS method over the scripting object method. The DOS method that I used was posted at: http://www.vbaexpress.com/forum/showthread.php?t=22245

[ASA]

Mr. Hobs,

The part of the code from where this sub of SearchFiles(strStartPath) is called has only the user choice of assigning the directory path (where search is to be done) to variable strStartPath.

Leaving that part of the code aside, even if I directly feed the path to this variable in the code you have posted, it fails to detect zip files whereas all other file types are comfortably listed out!

As I said, your code did work for the first time and it also detected the zip files. But now it does not!!!

Can we have some rectification in the 'scripting object method' code instead of using the DOS code?
I am using Microsoft Office 2003 professional edition.


Regards,
ASA

[Kenneth Hobs]

The link that I posted has both the DOS and the filescripting methods.

Not sure why your filesearch method failed. Maybe you can post the xls. What path were you searching?

[ASA]

Mr. Hobs,

I actually overlooked the 'file scripting method' in the link you posted.

I used that code methodology and now my program readily detects the zip files.

My problem is solved. Thanks for your help and prompt responses.

Regards,
ASA