Searching Main Folder and ALL Sub-Folders for File

[deve8ore]

Hello,

We have a vendor that will supply us many files, and unfortunately will place them in different folders with no uniformity (within Windows Explorer).

I'd like to have the capability to find and open the file based on name, whether it's in a main folder, or one of the subfolders. I could use a wildcard based on the file I'm trying to open.

I.e. - File named "*Net Rebate*" will be in and of the following folders:

Main Folder
Subfolder 1
Subfolder 1a
Subfolder 1b
Subfolder 2
Subfolder 2a
Subfolder 2b

ect..... could be many subfolders.

How can I "Loop" through the main folder and all associated sub-folders to find a file "*Net Rebate*" and open it up?

So far I have:

On Error Resume Next
Dim i As Integer
With Application.FileSearch
.LookIn = ([RebDetail])
'* represents wildcard characters
.Filename = "*Net Rebate*.xls"
If .Execute > 0 Then 'Workbook exists
For i = 1 To .FoundFiles.Count
Workbooks.Open (.FoundFiles(i))
Next i
End If
End With

Any help would be appreciated!!!

Thank you.

[ubentook]

Option Compare Text 'REQUIRED

Sub OpenSpecificWorkbook()
'Ubentook - August 2008
On Error GoTo ThatHurt
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim strPath As String
Dim blnFlag As Boolean

Application.ScreenUpdating = False
' Specify the folder...
strPath = "C:\Documents and Settings\user\My Documents\" 'CHANGE

' Use Microsoft Scripting runtime.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)

' Check for file in the folder and open file.
For Each objFile In objFolder.Files
If objFile.Name Like "*Net Rebate*" Then 'CHANGE
blnFlag = True
Workbooks.Open objFile
Exit For
End If
Next 'objFile
If Not blnFlag Then Call FindFileInSubfolder(objFolder, blnFlag)
CloseOut:
On Error Resume Next
Application.StatusBar = False
Application.ScreenUpdating = True
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
Exit Sub
ThatHurt:
Beep
MsgBox "Error " & Err.Number & " " & Err.Description, , _
"PrintWorkbooks"
GoTo CloseOut
End Sub

Function FindFileInSubfolder(ByRef oParentFolder As Object, ByRef bflag As Boolean)
Dim oSubFolder As Object
Dim oFile As Object
For Each oSubFolder In oParentFolder.SubFolders
For Each oFile In oSubFolder.Files
If oFile.Name Like "*Net Rebate*" Then 'CHANGE
Workbooks.Open oFile
bflag = True
Exit For
End If
Next
If Not bflag Then
Call FindFileInSubfolder(oSubFolder, bflag)
Else
Exit For
End If
Next 'oSubFolder
End Function

[deve8ore]

Thanks SO much!!!
I cannot even say I was even close... so thanks, it saved me time!

[ubentook]

You are welcome.
FYI...
"Application.FileSearch" has occasional problems and was removed from xl2007.
I would never rely on it.

The "Dir" function can also be used.
However, its help file could be a little clearer.

I prefer the FileSystemObject as it pretty much uses plain english for its objects, properties and methods.

Thanks SO much!!!
I cannot even say I was even close... so thanks, it saved me time!

[deve8ore]

Quick follow-up question....

It appears that when the file is not in any folder (main or sub folders) the macro will go right through the code without generating an error.

I tried removing the "On error resume next", however still not generating an error.

How can I implement the code to give an error such as "File does not exist" if there is no folder with the respective name?

Thanks again!

[ubentook]

You should only use "On Error Resume Next" to check specific lines of code for errors. Do not use it as a general cure all for an entire procedure.

You are looking for a message not an error.
If the blnFlag variable is false then no file was found.
This code line at the end of the sub should work...
If Not blnFlag Then MsgBox "No file found"

Quick follow-up question....
It appears that when the file is not in any folder (main or sub folders) the macro will go right through the code without generating an error.

I tried removing the "On error resume next", however still not generating an error.

How can I implement the code to give an error such as "File does not exist" if there is no folder with the respective name?

Thanks again!

[deve8ore]

Thanks again.... your help is appreciated!