Results 1 to 15 of 15
  1. #1
    Beppe is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    33

    Searching files in a directory with specified name

    Hello everyone!



    I need to search files in a specified directory. The filename to be found is generated by the user thorugh many fields. Follows an example of filename:

    COM-CE7001AR0-N049-DAMMER-XXXX-XXXXXX-CAROPRESEX-170130-XXXX-XXXXXX-001-PASSIR-ACCE

    The search should be done by taking into account all fields of the filename. If for example all fields except the first (COM) are empty (substituted with "X" by the mask) then all the files starting with COM should be outlined (maybe in a listbox). It is something similar to the search done in the Windows search field.

    Feel free to ask me further info if needed.

    Thanks in advance!
    Beppe

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I use this,
    the user picks a file in the folder to use (optional)
    then the code scans the folder for the 'pattern' in the string name of the file,
    then loads them all into a table.

    Code:
    '------------------
    sub btnLoads_click()
    '------------------
    dim vFile, vDir
    dim i as integer
    
    vFile = UserPickFile("c:\my documents\")
    if vFile <> "" then
      i = InStrRev(vFile, "\")          
      If i > 0 Then   vDir = Left(vFile, i)
      getFilesInDir2List vDir
    endif
    end sub
    
    
    '------------------
    Public Function UserPickFile(pvPath, Optional ByVal pvFilter)
    '------------------
    Dim fD As Office.FileDialog
    Dim varFile
    
    Set fD = Application.FileDialog(msoFileDialogFilePicker)
    'With Application.FileDialog(msoFileDialogSaveAs)
    With fD
       ' Allow user to make multiple selections in dialog box
       .AllowMultiSelect = False
              
       ' Set the title of the dialog box.
       .Title = "Please select one or more files"
    
       ' Clear out the current filters, and add our own.
       .Filters.Clear
       '.Filters.Add "Access Databases", "*.mdb"
       .Filters.Add "All Files", "*.*"
       .Filters.Add "Excel files", "*.xls*"
       .Filters.Add "Documents", "*.doc*"
       .Filters.Add "Acrobat", "*.pdf"
       .Filters.Add "Image", "*.png"
    
            ' Show the dialog box. If the .Show method returns True, the
            ' user picked at least one file. If the .Show method returns
            ' False, the user clicked Cancel.
       If .show = True Then
          If .AllowMultiSelect Then
                 'MULTI SELECT version
                    'Loop through each file selected and add it to our list box.
                For Each varFile In .SelectedItems
                   'Me.FileList.AddItem varFile
                Next
          Else
             UserPickFile = .SelectedItems(1)
          End If
       End If
    End With
    Set fD = Nothing
    End Function
    
    
     'load files listed in  a folder
     'RETURN a text string for RowSource in value list
    '------------------
    Public sub getFilesInDir2List(ByVal pvDir, Optional ByVal pvPattern)
    '------------------
    Dim FSO, oFolder, oFile, oRX
    Dim colFiles 'As Collection
    Dim sTxt As String, sFile As String, sSql As String
    
    
    If IsMissing(pvPattern) Then pvPattern = "."
    
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = FSO.GetFolder(pvDir)
    
    EmptyTbl "tFileList"
    docmd.setwarnings  false
    
    Set colFiles = oFolder.Files
    For Each oFile In colFiles
        
      If InStr(oFile.Name, pvPattern) > 0 Then
            sSql = "Insert into tFileList (NAME) VALUES ('" & oFile.Name & "')"
            DoCmd.RunSQL sSql
            'sTxt = sTxt & oFile.Name & vbCrLf
      End If
    Next
    
    docmd.setwarnings  true
    Set oFile = Nothing
    Set oFolder = Nothing
    Set FSO = Nothing
    End Function

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Related to https://www.accessforums.net/showthread.php?t=68079

    Will file names always have all 13 segments? Maybe like:
    Code:
    strFN = Nz(Field1, "*") & "-" & Nz(Field2, "*") & "-" & ... & Nz(field13, "*") & "-"
    If Replace(strFN, Chr(42) & "-", "") <> "" Then
        strFN = Left(strFN, Len(strFN) - 1)
        If Dir("defined path\" & strFN & ".*") <> "" Then
            'do something such as list files in listbox
        End If
    End If
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Beppe is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    33
    Thanks for the prompt answer guys!
    I will try tomorrow and let you know!

    Beppe

  5. #5
    Beppe is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    33
    Quote Originally Posted by June7 View Post
    Related to https://www.accessforums.net/showthread.php?t=68079

    Will file names always have all 13 segments? Maybe like:
    Code:
    strFN = Nz(Field1, "*") & "-" & Nz(Field2, "*") & "-" & ... & Nz(field13, "*") & "-"
    If Replace(strFN, Chr(42) & "-", "") <> "" Then
        strFN = Left(strFN, Len(strFN) - 1)
        If Dir("defined path\" & strFN & ".*") <> "" Then
            'do something such as list files in listbox
        End If
    End If
    End Sub
    Yes the file names are always 13 fields...

    What is strFN? Maybe a variable set as string?

    Does it mean that I have to use this one in the button? How do I list files in listbox?

    Thx

    Beppe

  6. #6
    Beppe is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    33
    I'm trying this one (result should be like https://www.accessforums.net/showthread.php?t=68079)

    Code:
    strFN = Nz(Me.Testo549, "*") & "-" & Nz(Me.Testo551, "*") & "-" & Nz(Me.Testo555, "*") & "-" & Nz(Me.Testo557, "*") & "-" & Nz(Me.Testo558, "*") & "-" & Nz(Me.Testo559, "*") & "-" & Nz(Me.Testo560, "*") & "-" & Nz(Me.Testo561, "*") & "-" & Nz(Me.Testo562, "*") & "-" & Nz(Me.Testo563, "*") & "-" & Nz(Me.Testo565, "*") & "-" & Nz(Me.Testo566, "*")
    
    
    If Replace(strFN, Chr(42) & "-", "") <> "" Then
        strFN = Left(strFN, Len(strFN) - 1)
        If Dir("Z:\ArchivioN\" & strFN & ".*") <> "" Then
            Application.FollowHyperlink ("Z:\ArchivioN\")
            'list file in listbox
        End If
    End If
    It does not do any result when I put the same fields of the filename...

    Any idea?

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    strFN is just a String variable that holds the concatenated filename. I show building the filename in VBA, you can do that in textbox if you prefer and then just reference the textbox.

    Since your folder path is defined (I have forgotten what you used), user doesn't need to select from file dialog.

    Your file names are so long I am not sure a listbox will really be useful to users. The listbox will have to be so wide to display the full filename.

    ranman's code is writing records to a table and then that table can be used as the listbox RowSource. Alternatively, can use listbox AddItem method demonstrated in http://allenbrowne.com/ser-59.html. The combobox RowSourceType property would have to be set to ValueList. The following structure worked for me.

    Code:
    Sub ListFiles()
    Dim FSO, oFolder, oFile
    Dim strFN As String
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = FSO.GetFolder("defined path")
    strFN = Nz(Field1, "*") & "-" & Nz(Field2, "*") & "-" & ... & Nz(Field13, "*") & "-"
    If Replace(strFN, Chr(42) & "-", "") <> "" Then
        strFN = Left(strFN, Len(strFN) - 1) & ".*"
        If Dir("defined path\" & strFN) <> "" Then
            'do something such as list files in listbox
            For Each oFile In oFolder.Files
                If oFile.Name Like strFN Then
                    Forms!formname.listboxname.AddItem oFile.Name
                End If
            Next
        End If
    End If
    End Sub
    I assumed the procedure should run only if user makes a selection in at least one field. If that is not the case, modify code appropriately.
    Last edited by June7; 09-20-2017 at 12:14 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Beppe is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    33
    I tried the code suggested, but still does not work..I think the code is fine since I do not have debug errors

    Code:
    Private Sub Comando569_Click()
    
    
    Dim FSO, oFolder, oFile
    Dim strFN As String
    
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = FSO.GetFolder("Z:\ArchivioN\")
    
    
    strFN = Nz(Me.Testo549, "*") & "-" & Nz(Me.Testo551, "*") & "-" & Nz(Me.Testo555, "*") & "-" & Nz(Me.Testo557, "*") & "-" & Nz(Me.Testo558, "*") & "-" & Nz(Me.Testo559, "*") & "-" & Nz(Me.Testo560, "*") & "-" & Nz(Me.Testo561, "*") & "-" & Nz(Me.Testo562, "*") & "-" & Nz(Me.Testo563, "*") & "-" & Nz(Me.Testo565, "*") & "-" & Nz(Me.Testo566, "*")
    
    
    If Replace(strFN, Chr(42) & "-", "") <> "" Then
        strFN = Left(strFN, Len(strFN) - 1) & ".*"
        If Dir("Z:\ArchivioN\" & strFN) <> "" Then
            MsgBox "file found"
            For Each oFile In oFolder.Files
                If oFile.Name Like strFN Then
                    Forms!CODIFICA.Elenco570.AddItem oFile.Name
                End If
            Next
        End If
    End If
    
    
    End Sub
    where: "Comando" is "button", "Testo N°" is "textbox with N°", "CODIFICA" is the name of the form, "Elenco570" is the name of the listbox

    in this code, just to see if it works, I would like to show a Msg box "file found" when the file name matches the various fields, but nothing happens

  9. #9
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Right before the IF statement, add this line of code:
    Debug.Print Dir("Z:\ArchivioN" & strFN)
    See what is displayed in the Immediate window.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The concatenation needs trailing "-" for the code to work properly. Look at my example again. See that "-" on the end following Field13?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Beppe is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    33
    Just added the "-" at the end but still not working..it does not even show the Msgbox "file found"

    Code:
    Private Sub Comando569_Click()
    
    
    Dim FSO, oFolder, oFile
    Dim strFN As String
    
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = FSO.GetFolder("Z:\ArchivioN\")
    
    
    strFN = Nz(Me.Testo549, "*") & "-" & Nz(Me.Testo551, "*") & "-" & Nz(Me.Testo555, "*") & "-" & Nz(Me.Testo557, "*") & "-" & Nz(Me.Testo558, "*") & "-" & Nz(Me.Testo559, "*") & "-" & Nz(Me.Testo560, "*") & "-" & Nz(Me.Testo561, "*") & "-" & Nz(Me.Testo562, "*") & "-" & Nz(Me.Testo563, "*") & "-" & Nz(Me.Testo565, "*") & "-" & Nz(Me.Testo566, "*") & "-"
    
    
    If Replace(srtFN, Chr(42) & "-", "") <> "" Then
        strFN = Left(strFN, Len(strFN) - 1) & ".*"
        If Dir("Z:\ArchivioN\" & strFN) <> "" Then
            MsgBox "File found"
            For Each oFile In oFolder.Files
                If oFile.Name Like strFN Then
                    Forms!CODIFICA.Elenco570.AddItem oFile.Name
                End If
            Next
        End If
    End If
    
    
    End Sub
    any idea??

  12. #12
    Beppe is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    33
    I tried like you said before, substituting srtFN with Me.Testo544 and the line in if-then like the other post..and now it returns the msg box

    Code:
    Private Sub Comando569_Click()
    
    
    Dim FSO, oFolder, oFile
    Dim strFN As String
    
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = FSO.GetFolder("Z:\ArchivioN\")
    
    If Replace(Me.Testo544, Chr(42) & "-", "") <> "" Then
        strFN = Left(Me.Testo544, Len(Me.Testo544) - 1) & ".*"
        If Dir("Z:\ArchivioN\" & Me.Testo544 & ".*") <> "" Then
            MsgBox "File trovato"
            For Each oFile In oFolder.Files
                If oFile.Name Like Me.Testo544 Then
                    Forms!CODIFICA.Elenco570.AddItem oFile.Name
                End If
            Next
        End If
    End If
    
    
    
    End Sub
    But how do I vary the srtFN with Me.testo544 in this line "strFN = Left(Me.Testo544, Len(Me.Testo544) - 1) & ".*""?
    It seems like my error regards the strFN variable..

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Never did I say to substitute strFN with Me.Testo544.
    I said you can do the concatenation in code or textbox.
    In either case strFN is set to the concatenated string.
    Use strFN everywhere I did in my example.

    Are Testo544 and Elenco570 both on form CODIFICA? If yes, can use Me instead of Forms!CODIFICA.

    Assume the same concatenation is done in Testo554, including the "-" on the end.
    Code:
    strFN = Me.Testo544
    If Replace(strFN, Chr(42) & "-", "") <> "" Then
        strFN = Left(strFN, Len(strFN) - 1) & ".*"
        If Dir("Z:\ArchivioN\" & strFN) <> "" Then
            MsgBox "File trovato"
            For Each oFile In oFolder.Files
                If oFile.Name Like strFN Then
                    Forms!CODIFICA.Elenco570.AddItem oFile.Name
                End If
            Next
        End If
    End If
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Beppe is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    33
    Yes they are both in the form; but I have a little doubt: the form label is CODIFICA, but the form file name is different (CODIFICA FILEXX)...
    Just to be clear that testo544 is the texbox where all the fields comprising the "-" are saved.

    I try like this but still not showing msgbox:

    Code:
    Private Sub Comando569_Click()
    
    Dim FSO, oFolder, oFile
    Dim strFN As String
    
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = FSO.GetFolder("Z:\ArchivioN\")
    
    
    strFN = Me.Testo544
    
    
    If Replace(strFN, Chr(42) & "-", "") <> "" Then
        strFN = Left(strFN, Len(strFN) - 1) & ".*"
        If Dir("Z:\ArchivioN\" & strFN) <> "" Then
            MsgBox "File trovato"
            For Each oFile In oFolder.Files
                If oFile.Name Like strFN Then
                    Me.Elenco570.AddItem oFile.Name
                End If
            Next
        End If
    End If

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The code works for me.

    When user makes selections they must exit the last combobox they make selection in so the combobox is updated with the selection.

    From here, you will have to debug. Set breakpoint, step through the code, is strFN receiving correct value?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 09-18-2017, 11:14 AM
  2. list files in a directory
    By alfrval in forum Access
    Replies: 2
    Last Post: 02-25-2015, 12:46 PM
  3. list all files in a directory
    By snipe in forum Programming
    Replies: 5
    Last Post: 01-21-2014, 12:18 PM
  4. Replies: 6
    Last Post: 10-26-2012, 12:53 PM
  5. Replies: 6
    Last Post: 06-15-2011, 04:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums