none
Need help to give the user the ability to search selected folder for a “Like” documents RRS feed

  • Question

  • Hello,

    Can you help me with this; I am not Access programmer and stuck on this. Using Access 2010

    I have a form “frmMain”, added a combo box “cboFolderLocation”, a  text box “txtLikeSearch”, a button  “cmdSearch”, and list box “ListDoc”.

    Need help to give the user the ability to select the combo box “cboFolderLocation”, select where the folder is located for example (M:\Department\Training\Letters).  Then key a word of the document in the text box “txtLikeSearch”, and press button “cmdSearch”.

    VBA needs to find all “Like” document names that match user selection from “cboFolderLocation” and  text box “txtLikeSearch”.  Then it populates those documents in list box “ListFiles” on the same form.  Also give the user the ability to select the appropriate document from “ListFiles” to open it in Microsoft word.

    Thank you very much!


    JayZ






    • Edited by Radio Fixer Wednesday, February 28, 2018 9:44 PM
    Tuesday, February 27, 2018 9:49 PM

Answers

  • Hello Antique Radio,

    It seems that there are some other list boxes in your form. Not very clear their functions.

    Based on your description, I think below code may be helpful for you.

    Note that, I used double click event to open the file in list box. If you want to use click event, just copy the code into a click event.

    Option Compare Database
    Option Explicit
    Dim sMyDoc As String
    Dim sFileName As String
    Dim sDrive As String
    Dim sProduct As String
    Dim sFolder As String
    
    Private Sub cmdSearch_Click()
    Dim strFile As String
       
    ' Add drives to combo box
        cboFolderLocation.RowSourceType = "Value List"
    
        cboFolderLocation.RowSource = ""
    
    'Server Folder
        cboFolderLocation.AddItem "C:\Users\v-guaxu\Desktop\TestFolder"
        cboFolderLocation.Value = "C:\Users\v-guaxu\Desktop\TestFolder"
           
    strFile = Dir(cboFolderLocation.Value & "\*" & txtLikeSearch & "*.docx")
    Me.ListDoc.RowSourceType = "Value List"
    Me.ListDoc.RowSource = ""
    Do While strFile <> ""
        Me.ListDoc.AddItem strFile
        strFile = Dir
    Loop
    End Sub
    
    'double click listbox to open selected item
    Private Sub ListDoc_DblClick(Cancel As Integer)
    Dim iSelectedRow
    
        iSelectedRow = ListDoc.ListIndex
    
        sMyDoc = ListDoc.Column(0, iSelectedRow)
        
        sFileName = cboFolderLocation & "\" & sMyDoc
    
        sProduct = Right(sMyDoc, 3)
      
        If sProduct = "doc" Or sProduct = "ocx" Then
            Application.FollowHyperlink sFileName
        End If
    End Sub
    

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Radio Fixer Monday, March 5, 2018 8:50 PM
    Wednesday, February 28, 2018 5:53 AM
  • You are clearing out the data for each file. I don't think you need to reset RowSource for each file, seems like both of those two lines at the end of this snippet should just be done one time, if you are wanting to put ALL files in the list. I would move those two lines into the CLICK event before calling the subroutine to clear out the list before you load it with all the data you find.

    For Each File In Folder.Files
            ' Operate on each file
            strFile = Dir(Folder & "\*" & txtLikeSearch & "*.docx")
            Me.ListDoc.RowSourceType = "Value List"
            Me.ListDoc.RowSource = ""


    Larry

    • Proposed as answer by Terry Xu - MSFT Thursday, March 1, 2018 5:51 AM
    • Marked as answer by Radio Fixer Monday, March 5, 2018 8:52 PM
    Wednesday, February 28, 2018 10:19 PM

All replies

  • Hi JayZ,

    The Dir() command can accept wildcard characters, which should give you the "like search" result you're looking for and populate your Listbox.

    I recommend using a Value List for the Listbox, so you can use the AddItem method to add the file found during the search to the Listbox.

    Here' s a quick example:

    Dim strFile As String
    
    strFile = Dir(cboFolderLocation & "\*" & txtLikeSearch & "*.docx")
    
    Do While strFile <> ""
        Me.ListboxName.AddItem strFile
        Dir()
    Loop

    To open the selected file, you can use the AfterUpdate event of the Listbox with something like the following:

    Application.FollowHyperlink Me.ListboxName

    Hope it helps...

    Tuesday, February 27, 2018 10:03 PM
  • Thank you for quick response. I added my code and your suggestion above, what am I missing? Am I building a monster?  LOL Thanks

    JayZ

    Tuesday, February 27, 2018 11:39 PM
  • Hello Antique Radio,

    It seems that there are some other list boxes in your form. Not very clear their functions.

    Based on your description, I think below code may be helpful for you.

    Note that, I used double click event to open the file in list box. If you want to use click event, just copy the code into a click event.

    Option Compare Database
    Option Explicit
    Dim sMyDoc As String
    Dim sFileName As String
    Dim sDrive As String
    Dim sProduct As String
    Dim sFolder As String
    
    Private Sub cmdSearch_Click()
    Dim strFile As String
       
    ' Add drives to combo box
        cboFolderLocation.RowSourceType = "Value List"
    
        cboFolderLocation.RowSource = ""
    
    'Server Folder
        cboFolderLocation.AddItem "C:\Users\v-guaxu\Desktop\TestFolder"
        cboFolderLocation.Value = "C:\Users\v-guaxu\Desktop\TestFolder"
           
    strFile = Dir(cboFolderLocation.Value & "\*" & txtLikeSearch & "*.docx")
    Me.ListDoc.RowSourceType = "Value List"
    Me.ListDoc.RowSource = ""
    Do While strFile <> ""
        Me.ListDoc.AddItem strFile
        strFile = Dir
    Loop
    End Sub
    
    'double click listbox to open selected item
    Private Sub ListDoc_DblClick(Cancel As Integer)
    Dim iSelectedRow
    
        iSelectedRow = ListDoc.ListIndex
    
        sMyDoc = ListDoc.Column(0, iSelectedRow)
        
        sFileName = cboFolderLocation & "\" & sMyDoc
    
        sProduct = Right(sMyDoc, 3)
      
        If sProduct = "doc" Or sProduct = "ocx" Then
            Application.FollowHyperlink sFileName
        End If
    End Sub
    

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Radio Fixer Monday, March 5, 2018 8:50 PM
    Wednesday, February 28, 2018 5:53 AM
  • Thank you for quick response. I added my code and your suggestion above, what am I missing? Am I building a monster?  LOL Thanks

    JayZ

    Hi JayZ,

    Can you post how your code looks now?

    Wednesday, February 28, 2018 4:08 PM
  • Why can't you just use the ACCESS built-in FilePicker? You can filter which directories and files are available to select. For example the following FilePicker lists all .docx, .txt and .doc files in C:\My Documents and opens the file selected:

    Dim SelectDialog As Office.FileDialog
    Dim SelectFile As Variant
    Set SelectDialog = Application.FileDialog(msoFileDialogFilePicker)
    With SelectDialog
       .AllowMultiSelect = False
       .Title = "Select A File"
       .InitialFileName = "C:\My Documents\" '   "M:\Document\Training\Letters\"
       .ButtonName = "Select This File"
        .Filters.Clear
        .Filters.Add "All Files", "*.docx; *.txt; *.doc", 1
       If .Show = True Then
          For Each SelectFile In .SelectedItems
            FollowHyperlink SelectFile
            Exit Sub (or Exit Function)
          Next
       End If
     End With

    Just replace C:\My Documents\ with M:\Document\Training\Letters\

    You can also include any files or extension you wish by editing the .Filters.Add parameter.


    Wednesday, February 28, 2018 5:11 PM
  • Terry,

    Thank you so much! You guys are genius!  You actually added a gif for me, I truly appreciate you valuable time and dedication to help others.

    Quick question: I have several subfolders under “Letters” folder and would like VBA to check all subfolder within M:\Department\Training\Letters\” to find the document.

    For example: M:\Department\Training\Letters\NewStaff\”, M:\Department\Training\Letters\CurrentStaff\”, M:\Department\Training\Letters\Customer\”

    What is the best way to do that?

    Thank you again 


    JayZ

    Wednesday, February 28, 2018 7:02 PM
  • For example: M:\Department\Training\Letters\NewStaff\”, M:\Department\Training\Letters\CurrentStaff\”, M:\Department\Training\Letters\Customer\”

    What is the best way to do that?

    Hi Jay,

    Everyone can have a different "best way".

    In a rather big project I made a table in Access that contains all the filenames of the documents and the path to the file. With the standard search queries you can find what you want, independant of the map where the file is stored. Pressing a command button will show the document.

    Nice thing about this is that add more fields to that table describing other properties of the document, e.g author, date of release, etc. But also you can build a 1:n relation to the document with headwords, entries for advanced searching.

    This way of working is applicable for documents, music, photo's, ...

    Imb. 

    Wednesday, February 28, 2018 7:36 PM
  • If you need to crawl a tree structure, that is going to take some real code and you will probably want to reference the Microsoft Scripting Runtime and use the Scripting.FileSystemObject to make the job easier. It gives you the ability to find folders, files and open them as needed to go through the entire tree.

    There are plenty of examples if you do a search on the web of how to crawl a folder structure as well as many examples of using the FileSystemObject.


    Larry

    Wednesday, February 28, 2018 7:41 PM
  • Great question!

    Lawrence, thank you for responding to my request.

    The location of these documents is hidden from the user.  There are many subfolders under “Letter” folder (M:\Department\Training\Letters), and each subfolder might have 50 to 100 documents. I am trying to give the user the ability to search the entire “letter” folder to find the documents that are interested to see by key some information in “txtLikeSearch” and Access display a list of related documents, and then user can view the appropriate document.  

    Have a great day!


    JayZ

    Wednesday, February 28, 2018 7:44 PM
  • I went back in some of my old code and found an example database (written in 2001) that crawls the specified tree structure and places the full path of every file into a table. I tested it with my Access 2016 and it still works. :)

    It contains about 100 lines of code (including comments and formatting), split over three subroutines/functions. I know you said you were not an Access Programmer, so I didn't post it anywhere. But if you really needed to so something like this, and you don't find what you need elsewhere, I can post it and you can try to modify it for your needs.


    Larry


    • Edited by llj69 Wednesday, February 28, 2018 8:32 PM
    Wednesday, February 28, 2018 8:21 PM
  • I used all recommendations and this is what I came up with since the location of these documents is hidden from the user I removed combo box “cboFolderLocation”. 

    There are many subfolders under “Letter” folder (M:\Department\Training\Letters), and each subfolder might have 50 to 100 documents. I am trying to give the user the ability to search the entire “letter” folder to find the documents that are interested to see by key some information in “txtLikeSearch” and Access display a list of related documents, and then user can view the appropriate document.

    The code is running and it finds select documents and it list them but it overwrite itself when cannot find more document in other folders.  What am I missing?

    This is what I am using now:
     
    Option Compare Database
    Option Explicit
    Dim sMyDoc As String
    Dim sFileName As String
    Dim sDrive As String
    Dim sProduct As String
    Dim sFolder As String
    Dim strFile As String

    ‘**********************************************************

    Private Sub cmdSearch_Click()
    Dim FileSystem As Object
    Dim HostFolder As String

    HostFolder = "M:\Document\Training\Letters\"

    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    DoFolder FileSystem.GetFolder(HostFolder)
    End Sub

    ‘**********************************************************

    Sub DoFolder(Folder)
        Dim SubFolder
        For Each SubFolder In Folder.SubFolders
            DoFolder SubFolder
        Next
        Dim File
        For Each File In Folder.Files
            ' Operate on each file
            strFile = Dir(Folder & "\*" & txtLikeSearch & "*.docx")
            Me.ListDoc.RowSourceType = "Value List"
            Me.ListDoc.RowSource = ""
           
            Do While strFile <> ""
                Me.ListDoc.AddItem strFile
                strFile = Dir
            Loop
        Next
    End Sub

    ‘**********************************************************

    'double click listbox to open selected item
    Private Sub ListDoc_DblClick(Cancel As Integer)
    Dim iSelectedRow

        iSelectedRow = ListDoc.ListIndex

        sMyDoc = ListDoc.Column(0, iSelectedRow)

        sFileName = CboDrives & "\" & sMyDoc

        sProduct = Right(sMyDoc, 3)

        If sProduct = "doc" Or sProduct = "ocx" Then
            Application.FollowHyperlink sFileName
        End If
    End Sub


    JayZ



    • Edited by Radio Fixer Wednesday, February 28, 2018 10:06 PM
    • Proposed as answer by Terry Xu - MSFT Thursday, March 1, 2018 5:51 AM
    Wednesday, February 28, 2018 10:02 PM
  • Hi JayZ,

    Glad to hear you got it sorted out. Good luck with your project.

    Wednesday, February 28, 2018 10:10 PM
  • You are clearing out the data for each file. I don't think you need to reset RowSource for each file, seems like both of those two lines at the end of this snippet should just be done one time, if you are wanting to put ALL files in the list. I would move those two lines into the CLICK event before calling the subroutine to clear out the list before you load it with all the data you find.

    For Each File In Folder.Files
            ' Operate on each file
            strFile = Dir(Folder & "\*" & txtLikeSearch & "*.docx")
            Me.ListDoc.RowSourceType = "Value List"
            Me.ListDoc.RowSource = ""


    Larry

    • Proposed as answer by Terry Xu - MSFT Thursday, March 1, 2018 5:51 AM
    • Marked as answer by Radio Fixer Monday, March 5, 2018 8:52 PM
    Wednesday, February 28, 2018 10:19 PM
  • Hello Antique Radio,

    I'm glad to hear that you have solved the issue. I would suggest you mark helpful reply as answer to close this thread.

    For the issue overwriting itself, just as  Larry said, do not call  Me.ListDoc.RowSource = "" in DoFolder method since it will reset the list box each time recursiving to call the method.

    Thanks for understanding.
    Best Regards,
    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 1, 2018 6:47 AM
  • Hi Larry,

    Changed around some coding and now is pulling documents but there is two problems.  

    1. It pulls all documents in entire “Training” folder and subfolders guess it does not recognize “txtLikeSearch”.  This is the code “ strFile = Dir(Folder & "\*" & txtLikeSearch & "*.docx")”.

    2. Also it doesn’t open the Word document due to this line of code “  sFileName = ListDoc & "\" & sMyDoc”

    Option Compare Database

    Option Explicit

    Dim sMyDoc As String

    Dim sFileName As String

    Dim sDrive As String

    Dim sProduct As String

    Dim sFolder As String

    Dim strFile As String

    Private Sub cmdSearch_Click()

    Dim FileSystem As Object

    Dim HostFolder As String

    'HostFolder = "M:\Document\Training\Letters\"

    Me.ListDoc.RowSource = ""

    Set FileSystem = CreateObject("Scripting.FileSystemObject")

    DoFolder FileSystem.GetFolder(HostFolder)

    End Sub

    Sub DoFolder(Folder)

        Dim SubFolder

        For Each SubFolder In Folder.SubFolders

            DoFolder SubFolder

        Next

            Dim Files

            For Each Files In Folder.Files

                ' Operate on each file

               strFile = Dir(Folder & "\*" & txtLikeSearch & "*.docx")

                    ListDoc.RowSourceType = "Value List"

                    ListDoc.AddItem strFile

        Next

    End Sub

    'double click listbox to open selected item

    Private Sub ListDoc_DblClick(Cancel As Integer)

    Dim iSelectedRow

        iSelectedRow = ListDoc.ListIndex

        sMyDoc = ListDoc.Column(0, iSelectedRow)

      sFileName = ListDoc & "\" & sMyDoc

        sProduct = Right(sMyDoc, 3)

        If sProduct = "doc" Or sProduct = "ocx" Then

            Application.FollowHyperlink sFileName

        End If

    End Sub


    JayZ

    Thursday, March 1, 2018 11:12 PM
  • First, you should be able to move the line "ListDoc.RowSourceType = "Value List"" out of the loop, up before the line where you have "Me.ListDoc.RowSource = """. You are setting the listbox to a Value List (which you could really do as a property on the form, unless it changes) and then clearing any values in it. So it only needs to be done one time.

    Since you are going to touch each file in the folder anyway, the DIR command isn't what you really want anyway. Change the line "strFile = Dir(Folder & "\*" & txtLikeSearch & "*.docx")" with the following lines

       If InStr(fil.Name, strSearch) > 0 And Right(fil.Name, 4) = "docx" Then

          strFile = fil.Name

       end if

    This will search for the search text (without the wildcards) and if it exists in the file name AND the right 4 characters show it's a Word document, then you have the file to do with what you want.

    For the 2nd question, I'm not real sure what you are trying to do with "ListDoc & "\" & sMyDoc", since ListDoc is the name of your listbox. If you store the full path and file name in the listbox, then you should just need the "sMyDoc" variable and let the FollowHyperlink open that.

    Have you used Debug to see what it's trying to create when you use that "ListDoc & "\" & sMyDoc" line? I imagine it throws and error, but if it doesn't, it'd be interesting to see what is in sFileName after that line is executed.


    Larry

    • Marked as answer by Radio Fixer Monday, March 5, 2018 8:50 PM
    • Unmarked as answer by Radio Fixer Monday, March 5, 2018 8:51 PM
    Friday, March 2, 2018 2:08 AM