I need a VBA command that will find file xxx.xls no matter were it is located
on the harddrive.

I found information in the VBA help under searchfolder options. I've been
told that Filesearch function has bugs in 2007.

SearchFolders Collection
See AlsoPropertiesMethodsEventsSpecificsFileSearch
SearchFolders
ScopeFolder
ScopeFolders

A collection of ScopeFolder objects that determines which folders are
searched when the Execute method of the FileSearch object is called.

Using the SearchFolders collection
Use the SearchFolders property with the FileSearch object to return the
SearchFolders collection; for example:

Set sfs = Application.FileSearch.SearchFolders

For each application there is only a single SearchFolders collection. The
contents of the collection remains after the code that calls it has finished
executing. Consequently, it is important to clear the collection unless you
want to include folders from previous searches in your search.

You can use the Add method of the SearchFolders collection to add a
ScopeFolder object to the SearchFolders collection, however, it is usually
simpler to use the AddToSearchFolders method of the ScopeFolder that you want
to add, as there is only one SearchFolders collection for all searches.

The SearchFolders collection can be seen as a compliment to the LookIn
property of the FileSearch object. Both specify the folders to search and
both are used when the search is executed. However, if you only want to use
the LookIn property, you should make sure that the SearchFolders collection
is empty. Conversely, if you only want to use the SearchFolders collection,
set the LookIn property to the path of the first member of the SearchFolders
collection before you call the Execute method.

The following example searches every folder named "1033" on the local
machine for all HTML and Microsoft Excel files. The example makes use of the
SearchFolders collection, SearchScopes collection, and ScopeFolders
collection.

This example consists of two routines. The SearchEveryFolder routine is the
routine that you should run. The OutputPaths routine is separate from the
main routine because it calls itself recursively in order to traverse the
entire directory structure of the local machine.

Sub SearchEveryFolder()

'Declare variables that reference a
'SearchScope and a ScopeFolder object.
Dim ss As SearchScope
Dim sf As ScopeFolder

'Declare a variable to act as a generic counter.
Dim lngCount As Long

'Use a With...End With block to reference the
'FileSearch object.
With Application.FileSearch

'Clear all the parameters of the previous searches.
'This method doesn't clear the LookIn property or
'the SearchFolders collection.
.NewSearch

'Specify the type of file for which to search.
'Use the FileType property to specify the first type
'and then add additional types to the FileTypes collection.
.FileType = msoFileTypeWebPages
.FileTypes.Add msoFileTypeExcelWorkbooks

'Clear the SearchFolder collection by
'looping through each ScopeFolder object
'and removing it.
For lngCount = 1 To .SearchFolders.Count
.SearchFolders.Remove lngCount
Next lngCount

'Loop through the SearchScopes collection to find
'the scope in which you want to search. In this
'case the scope is the local machine.
For Each ss In .SearchScopes
Select Case ss.Type
Case msoSearchInMyComputer

'Loop through each ScopeFolder in
'the ScopeFolders collection of the
'SearchScope object.
For Each sf In ss.ScopeFolder.ScopeFolders

'Call a function that loops through all
'of the subfolders of the root ScopeFolder.
'This function adds any folders named "1033" to the
'SearchFolders collection.
Call OutputPaths(sf.ScopeFolders, "1033")

Next sf
Case Else
End Select
Next ss

'Test to see if any ScopeFolders collections were added to
'the SearchFolders collection.
If .SearchFolders.Count 0 Then

'Set the LookIn property to the path of
'the first ScopeFolder object in the SearchFolders
'collection. This is here so that any previous
'setting of the LookIn property doesn't affect
'the search.
.LookIn = .SearchFolders.Item(1).Path

'Execute the search and test to see if any files
'were found.
If .Execute < 0 Then

'Display the number of files found.
MsgBox "Files found: " & .FoundFiles.Count

'Loop through the list of found files and
'display the path of each one in a message box.
For lngCount = 1 To .FoundFiles.Count
If MsgBox(.FoundFiles.Item(lngCount), vbOKCancel, _
"Found files") = vbCancel Then

'Break out of the loop
lngCount = .FoundFiles.Count

End If
Next lngCount
End If
End If
End With
End Sub

'This subroutine loops through all of the ScopeFolders collections
'in a given ScopeFolders collection. It adds any folder
'that has the same name as the value of strFolder
'to the SearchFolders collection.
Sub OutputPaths(ByVal sfs As ScopeFolders, _
ByRef strFolder As String)

'Declare a variable as a ScopeFolder object
Dim sf As ScopeFolder

'Loop through each ScopeFolder object in the
'ScopeFolders collection.
For Each sf In sfs

'Test to see if the folder name of the ScopeFolder
'matches the value of strFolder. Use LCase to ensure
'that case does not affect the match.
If LCase(sf.Name) = LCase(strFolder) Then

'Add the ScopeFolder to the SearchFolders collection.
sf.AddToSearchFolders

End If

'Include a DoEvents call because there is the potential for this
'loop to last a long time. The DoEvents call allows this process to
'continue handling events.
DoEvents

'Test to see if the ScopeFolders collection in the
'current ScopeFolder is empty. If it isn't empty, then
'that means that the current ScopeFolder object contains subfolders.
If sf.ScopeFolders.Count 0 Then

'This subroutine recursively calls itself so that
'it can add the subfolders of the current ScopeFolder object
'to the SearchFolders collection.
Call OutputPaths(sf.ScopeFolders, strFolder)

End If
Next sf
End Sub



"Megadrone" wrote:

I need a VBA command that will find file xxx.xls no matter were it is located
on the harddrive.

told that Filesearch function has bugs in 2007.

Not exist anymore in 2007
Bugs in all older versions (miss files and have problems with .zip)


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Joel" wrote in message ...
I found information in the VBA help under searchfolder options. I've been
told that Filesearch function has bugs in 2007.

SearchFolders Collection
See AlsoPropertiesMethodsEventsSpecificsFileSearch
SearchFolders
ScopeFolder
ScopeFolders

A collection of ScopeFolder objects that determines which folders are
searched when the Execute method of the FileSearch object is called.

Using the SearchFolders collection
Use the SearchFolders property with the FileSearch object to return the
SearchFolders collection; for example:

Set sfs = Application.FileSearch.SearchFolders

For each application there is only a single SearchFolders collection. The
contents of the collection remains after the code that calls it has finished
executing. Consequently, it is important to clear the collection unless you
want to include folders from previous searches in your search.

You can use the Add method of the SearchFolders collection to add a
ScopeFolder object to the SearchFolders collection, however, it is usually
simpler to use the AddToSearchFolders method of the ScopeFolder that you want
to add, as there is only one SearchFolders collection for all searches.

The SearchFolders collection can be seen as a compliment to the LookIn
property of the FileSearch object. Both specify the folders to search and
both are used when the search is executed. However, if you only want to use
the LookIn property, you should make sure that the SearchFolders collection
is empty. Conversely, if you only want to use the SearchFolders collection,
set the LookIn property to the path of the first member of the SearchFolders
collection before you call the Execute method.

The following example searches every folder named "1033" on the local
machine for all HTML and Microsoft Excel files. The example makes use of the
SearchFolders collection, SearchScopes collection, and ScopeFolders
collection.

This example consists of two routines. The SearchEveryFolder routine is the
routine that you should run. The OutputPaths routine is separate from the
main routine because it calls itself recursively in order to traverse the
entire directory structure of the local machine.

Sub SearchEveryFolder()

'Declare variables that reference a
'SearchScope and a ScopeFolder object.
Dim ss As SearchScope
Dim sf As ScopeFolder

'Declare a variable to act as a generic counter.
Dim lngCount As Long

'Use a With...End With block to reference the
'FileSearch object.
With Application.FileSearch

'Clear all the parameters of the previous searches.
'This method doesn't clear the LookIn property or
'the SearchFolders collection.
.NewSearch

'Specify the type of file for which to search.
'Use the FileType property to specify the first type
'and then add additional types to the FileTypes collection.
.FileType = msoFileTypeWebPages
.FileTypes.Add msoFileTypeExcelWorkbooks

'Clear the SearchFolder collection by
'looping through each ScopeFolder object
'and removing it.
For lngCount = 1 To .SearchFolders.Count
.SearchFolders.Remove lngCount
Next lngCount

'Loop through the SearchScopes collection to find
'the scope in which you want to search. In this
'case the scope is the local machine.
For Each ss In .SearchScopes
Select Case ss.Type
Case msoSearchInMyComputer

'Loop through each ScopeFolder in
'the ScopeFolders collection of the
'SearchScope object.
For Each sf In ss.ScopeFolder.ScopeFolders

'Call a function that loops through all
'of the subfolders of the root ScopeFolder.
'This function adds any folders named "1033" to the
'SearchFolders collection.
Call OutputPaths(sf.ScopeFolders, "1033")

Next sf
Case Else
End Select
Next ss

'Test to see if any ScopeFolders collections were added to
'the SearchFolders collection.
If .SearchFolders.Count 0 Then

'Set the LookIn property to the path of
'the first ScopeFolder object in the SearchFolders
'collection. This is here so that any previous
'setting of the LookIn property doesn't affect
'the search.
.LookIn = .SearchFolders.Item(1).Path

'Execute the search and test to see if any files
'were found.
If .Execute < 0 Then

'Display the number of files found.
MsgBox "Files found: " & .FoundFiles.Count

'Loop through the list of found files and
'display the path of each one in a message box.
For lngCount = 1 To .FoundFiles.Count
If MsgBox(.FoundFiles.Item(lngCount), vbOKCancel, _
"Found files") = vbCancel Then

'Break out of the loop
lngCount = .FoundFiles.Count

End If
Next lngCount
End If
End If
End With
End Sub

'This subroutine loops through all of the ScopeFolders collections
'in a given ScopeFolders collection. It adds any folder
'that has the same name as the value of strFolder
'to the SearchFolders collection.
Sub OutputPaths(ByVal sfs As ScopeFolders, _
ByRef strFolder As String)

'Declare a variable as a ScopeFolder object
Dim sf As ScopeFolder

'Loop through each ScopeFolder object in the
'ScopeFolders collection.
For Each sf In sfs

'Test to see if the folder name of the ScopeFolder
'matches the value of strFolder. Use LCase to ensure
'that case does not affect the match.
If LCase(sf.Name) = LCase(strFolder) Then

'Add the ScopeFolder to the SearchFolders collection.
sf.AddToSearchFolders

End If

'Include a DoEvents call because there is the potential for this
'loop to last a long time. The DoEvents call allows this process to
'continue handling events.
DoEvents

'Test to see if the ScopeFolders collection in the
'current ScopeFolder is empty. If it isn't empty, then
'that means that the current ScopeFolder object contains subfolders.
If sf.ScopeFolders.Count 0 Then

'This subroutine recursively calls itself so that
'it can add the subfolders of the current ScopeFolder object
'to the SearchFolders collection.
Call OutputPaths(sf.ScopeFolders, strFolder)

End If
Next sf
End Sub



"Megadrone" wrote:

I need a VBA command that will find file xxx.xls no matter were it is located
on the harddrive.