Access 2010 and filesearch property

Posted on 2011-05-06
Medium Priority
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

ID: 35710855
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


Author Comment

ID: 35710863
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

ID: 35711148
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.
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

LVL 26

Expert Comment

ID: 35711244
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

peter57r earned 2000 total points
ID: 35711260
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.


Author Closing Comment

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

850 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