PDA

View Full Version : Solved: Need help modifying this code



majaro
02-04-2008, 01:55 PM
I found this code on the Knowledge Base and when I run it the last modified date and time stamp is missing however there is a column for it. Everything else is fine except that. Is the code incorrect?

Bob Phillips
02-04-2008, 03:23 PM
Sub SrchForFiles()
' Searches the selected folders and sub folders for files with the specified
'extension. .xls, .doc, .ppt, etc.
'A new worksheet is produced called "File Search Results". You can click on the link and go directly
'to the file you need.
Dim i As Long, z As Long, ws As Worksheet, y As Variant
Dim fLdr As String
y = Application.InputBox("Please Enter File Extension", "Info Request")
If y = False And Not TypeName(y) = "String" Then Exit Sub
Application.ScreenUpdating = False
fLdr = BrowseForFolderShell
With Application.FileSearch
.NewSearch
.LookIn = fLdr
.SearchSubFolders = True
.Filename = y
Set ws = ThisWorkbook.Worksheets.Add(Sheets(1))
On Error GoTo 1
2: ws.Name = "FileSearch Results"
On Error GoTo 0
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
If Left$(.FoundFiles(i), 1) = Left$(fLdr, 1) Then
If CBool(Len(Dir(.FoundFiles(i)))) Then
z = z + 1
ws.Cells(z + 1, 1).Resize(, 3) = _
Array(Dir(.FoundFiles(i)), _
FileLen(.FoundFiles(i)) \ 1000, _
FileDateTime(.FoundFiles(i)))
ws.Hyperlinks.Add Anchor:=Cells(z + 1, 1), _
Address:=.FoundFiles(i)
End If
End If
Next i
End If
End With

ActiveWindow.DisplayHeadings = False

With ws
With .[a1:c1 ]
.Value = [{"Full Name","Kilobytes","Last Modified"}]
.Font.Underline = xlUnderlineStyleSingle
.EntireColumn.AutoFit
.HorizontalAlignment = xlCenter
End With
.[d1:iv1 ].EntireColumn.Hidden = True
Range(.[a65536 ].End(3)(2), _
.[a65536 ]).EntireRow.Hidden = True
Range(.[a2 ], .[c65536 ]).Sort [a2 ], xlAscending, Header:=xlNo
End With

Application.ScreenUpdating = True
Exit Sub

1: Application.DisplayAlerts = False
Worksheets("FileSearch Results").Delete
Application.DisplayAlerts = True
GoTo 2
End Sub

majaro
02-05-2008, 11:32 AM
As with everything, I need to get some help modifying this. I need to add the root folder name and the sub folder name on each line of the listing.