Access 2010 and filesearch property

Posted on 2011-05-06
Last Modified: 2012-06-27
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

Question by:OGSan
    LVL 42

    Expert Comment

    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

    LVL 1

    Author Comment

    Thanks for the reply, dqmq.  Question: will this code work if the .pdf documents are inside sub-folders within the SUPPORTING DOCUMENTS folder?
    LVL 42

    Expert Comment

    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.
    LVL 26

    Expert Comment

    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

    LVL 77

    Accepted Solution

    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.
    LVL 1

    Author Closing Comment

    Thanks for this link, peter57r!  I was able to adapt the code to my needs and it's working fine now.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now