• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 670
  • Last Modified:

Access 2010 and filesearch property

Aloha, Experts!
I have to update an older 2003 mdb that was using the filesearch property in code attached to the autoexec macro (see below).  It would scan over the contents of a folder containing .pdf documents and build a list of all the .pdfs and make them available for viewing.  Can someone help me out and show me what needs to be changed in order to make this thing work with access 2010?  Thank you so much.
Function Fill_TempFileNames()
    'Dim MySearch As Office.FileSearch
    Dim intI As Integer, strPath As String
    Set MySearch = Application.FileSearch
    With MySearch
        .NewSearch  'initialize a new search
        .LookIn = strPath   'starting point for search
        .FileName = "*.pdf" 'filename...accepts wildcard character
        .SearchSubFolders = True    'scan folders inside first folder, set to False if not desired
        .Execute    '.FoundFiles will contain the list of PDF files found as a collection
        For intI = 1 To .FoundFiles.Count
            DoCmd.SetWarnings False
            DoCmd.RunSQL "Insert into tbl_TempFileNames(PDF_filename) values ('" & .FoundFiles(intI) & "')"
        Next intI
        DoCmd.SetWarnings True
    End With

End Function

Open in new window

1 Solution
Dim FolderLength As Integer
Dim fName As String
Dim objFSO As FileSystemObject
Dim objFolder As Folder
Dim objFile As File
Dim strPath as String

strPath = 'YourFilePath'
Set objFSO = New FileSystemObject
Set objFolder = objFSO.GetFolder(strPath)

For Each objFile In objFolder.Files
FolderLength = Len(StrPath) + 1
fName = Mid(objFile, FolderLength)

'Do your thing here ...
 DoCmd.SetWarnings False
 DoCmd.RunSQL "Insert into tbl_TempFileNames(PDF_filename) values ('" & .FoundFiles(intI) & "')"

Next objFile
docmd.setwarnings true

OGSanAuthor Commented:
Thanks for the reply, dqmq.  Question: will this code work if the .pdf documents are inside sub-folders within the SUPPORTING DOCUMENTS folder?
I don't believe so.  But filesystemobject has a .subfolders property that returns the subfolder names.  you could recursively iterate over the subfolders to explore the lower level directories.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

They deprecated FileSearch in A2007 :(
Here is code adapted from
That uses Dir and a collection
(about a third of the way down in the discussion)
There may be some bugs--created in the IDE but not tested
Sub File_Search()
    Dim Coll_Docs As New Collection
    Dim Search_path, Search_Filter, Search_Fullname As String
    Dim DocName As String
    Dim i As Long

    Search_path = "J:\HR\BENEFITS\OPT OUT CREDIT\SUPPORTING DOCS"   ' where ?
    Search_Filter = "*.pdf"       ' what ?
    Set Coll_Docs = Nothing
    DocName = Dir(Search_path & "\" & Search_Filter)
    Do Until DocName = ""            ' build the collection
        Coll_Docs.Add Item:=DocName
        DocName = Dir
    'MsgBox "There were " & Coll_Docs.Count & " file(s) found."
    For i = Coll_Docs.Count To 1 Step -1              '
        Search_Fullname = Search_path & "\" & Coll_Docs(i)
        DoCmd.SetWarnings False
        DoCmd.RunSQL "Insert into tbl_TempFileNames(PDF_filename) values ('" & Search_Fullname & "')"
 DoCmd.SetWarnings True
End Sub

Open in new window

This is a function that can list all files in a folder structure - it does not look inside zip files.
It should be easy enough to modify the code just to return a true/false if a specific file is found.

OGSanAuthor Commented:
Thanks for this link, peter57r!  I was able to adapt the code to my needs and it's working fine now.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now