FileSearch in Access 2007

[Anthony Ching]

I have a program that needs to search for files in individual folders.
However, the Application.FileSearch doesn't seem to be supported in access
2007.

Dim fs as object
Set fs = application.filesearch

I tried a different method using FileSystemObject. However, this doesn't
seem to be supported either.

Can someone help.

 

[Alex Dybenko]

Hi,
look here (second part)
http://www.mvps.org/access/api/api0006.htm

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Tom van Stiphout]

On Thu, 29 Jan 2009 18:20:00 -0800, Anthony Ching

I can't get that to work either. "You entered an expression that has
an invalid reference to the property FileSearch." I will investigate
further as time permits. The code here:
http://msdn.microsoft.com/en-us/library/aa190819(office.10).aspx is
not working for me either.

In the meantime, use the built-in Dir function to iterate over the
files in a folder.
Or create a Scripting.FileSystem object.

-Tom.
Microsoft Access MVP

 

[Allen Browne]

If you need to search in subfolders also, this code will do it:
List files recursively - List files in a folder and subfolders
at:
http://allenbrowne.com/ser-59.html

 

[Tom van Stiphout]

On Thu, 29 Jan 2009 21:36:52 -0700, Tom van Stiphout

The FileSearch property is deprecated.

Tom Wickerath suggested this article:
http://my.advisor.com/articles.nsf/aid/16279

-Tom.
Microsoft Access MVP

 

[PavHabr]

Replacement solution including searching in subdirectories

//------------------------------------------------------------------------------------------------

Sub FileSearchByHavrda_Example_of_procedure_calling()
'
' Example of FileSearchByHavrda procedure calling as replacement of missing FileSearch function in the newest MS Office VBA
' 01.06.2009, Author: P. Havrda, Czech Republic
'

Dim FileNameWithPath As Variant
Dim ListOfFilenamesWithParh As New Collection ' create a collection of filenames

' Filling a collection of filenames (search Excel files including subdirectories)
Call FileSearchByHavrda(ListOfFilenamesWithParh, "C:\Temp", "*.xls", True)

' Print list to immediate debug window and as a message window
For Each FileNameWithPath In ListOfFilenamesWithParh ' cycle for list(collection) processing
Debug.Print FileNameWithPath & Chr(13)
MsgBox FileNameWithPath & Chr(13)
Next FileNameWithPath

' Print to immediate debug window and message if no file was found
If ListOfFilenamesWithParh.Count = 0 Then
Debug.Print "No file was found !"
MsgBox "No file was found !"
End If

End Sub

//------------------------------------------------------------------------------------------------

Private Sub FileSearchByHavrda(pFoundFiles As Collection, pPath As String, pMask As String, pIncludeSubdirectories As Boolean)
'
' Search files in Path and create FoundFiles list(collection) of file names(path included) accordant with Mask (search in subdirectories if enabled)
' 01.06.2009, Author: P. Havrda, Czech Republic
'

Dim DirFile As String
Dim CollectionItem As Variant
Dim SubDirCollection As New Collection

' Add backslash at the end of path if not present
pPath = Trim(pPath)
If Right(pPath, 1) <> "\" Then pPath = pPath & "\"

' Searching files accordant with mask
DirFile = Dir(pPath & pMask)
Do While DirFile <> ""
pFoundFiles.Add pPath & DirFile 'add file name to list(collection)
DirFile = Dir ' next file
Loop

' Procedure exiting if searching in subdirectories isn't enabled
If Not pIncludeSubdirectories Then Exit Sub

' Searching for subdirectories in path
DirFile = Dir(pPath & "*", vbDirectory)
Do While DirFile <> ""
' Add subdirectory to local list(collection) of subdirectories in path
If DirFile <> "." And DirFile <> ".." Then If ((GetAttr(pPath & DirFile) And vbDirectory) = 16) Then SubDirCollection.Add pPath & DirFile
DirFile = Dir 'next file
Loop

' Subdirectories list(collection) processing
For Each CollectionItem In SubDirCollection
Call FileSearchByHavrda(pFoundFiles, CStr(CollectionItem), pMask, pIncludeSubdirectories) ' Recursive procedure call
Next

End Sub

//------------------------------------------------------------------------------------------------