File Searching

  • Thread starter Thread starter Lawson
  • Start date Start date
L

Lawson

hello,

im having trouble with this part of my program in that it
doesnt distinguish between FILES and FOLDERS. if there is
an excel FILE in this section of the folder with the same
name as the user has entered, it says it found one. i
only want it to return a positive value if it found a
FOLDER with the name entered by the user.


With Application.FileSearch
.LookIn = "\\Cpi-server-1
\estimate\Quotes\EST" & Range("q262") & "000\EST" & Range
("q263") & "00"
.Filename = Range("q261")
If .Execute > 0 Then
MsgBox "File '" & Range("q261") & "' already
exists with " & .FoundFiles.Count & " file(s) in it."
Else
 
Lawson,

I posted this a while ago. Watch for line wrapping problems introduced by
news readers....

HTH,
Bernie
MS Excel MVP

You need to use the FileSystemObject, which will require a reference set to
Microsoft Scripting Runtime.

The code below will find exact matches or any instance of the search name.

I wasn't sure what you wanted to do with the folder, so I just tell you
where they are.


Dim FileSysObject As Scripting.FileSystemObject
Dim RootFolderName As String
Dim fsoFolder As Scripting.Folder
Dim RootFolder As Scripting.Folder
Dim FindFolderName As String

Sub StartTree()

RootFolderName = "C:\" 'Change this to root folder
FindFolderName = "Excel" 'Change this to folder name

Set FileSysObject = New Scripting.FileSystemObject
Set RootFolder = FileSysObject.GetFolder(RootFolderName)

Tell RootFolder

Set FileSysObject = Nothing

End Sub

Sub Tell(fsoFolder As Scripting.Folder)
Dim sfldr As Scripting.Folder

For Each sfldr In fsoFolder.SubFolders
'Use this next line to find all instances of
FindFolderName in any subfolder
'Will find "Excel", "Excel Files", "My Excel Files", etc.
'Otherwise comment it out and uncomment the next line to
find exact matches
If UCase(sfldr.Name) Like "*" & UCase(FindFolderName) & "*" Then
' If UCase(sfldr.Name) = UCase(FindFolderName) Then
MsgBox "Found " & FindFolderName & ": " & sfldr.Path
If sfldr.SubFolders.Count > 0 Then
Tell sfldr
End If
End If
Next sfldr

End Sub
 
Back
Top