Listing names of files in folder on form

Anonymous
2020-06-11T01:02:31+00:00

I am having troubles trying to list on a form all files in a folder based on criteria.

I have tried both Allen Browne's solution and Daniel Pineault's solution, and they both seem* to work for ALL files in the folder.

What I need is the list to only show file names that start with a SKU # displayed in a text field ( [txtStockCode] ) on the form the listbox is on.

So something like - \folder\Archive BOM Copies\ & Me.[txtStockCode] & " *.htm "

For starters, would there be a better way to do this other than a listbox, like a sub-form?

If not, any ideas how I can filter the listbox based on what is in [txtStockCode] ?

Ultimately I want a clickable link to be able to open these files.

*I say seem to work as the file names that are displayed only show the name until there is a comma or parentheses in the file name. The files are saved to the folder as a concatenation of SKU+Description+Date(), and description commonly has comma's and occasionally parentheses. 

Also, it doesn't show all files in the list, there are ~2500 files in the folder and growing daily and it only scrolls down a few hundred. Maybe a non-issue as I only want it to be able to show the ones that match the text field.

Any help is appreciated, thanks!

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

68 additional answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2020-06-18T19:10:16+00:00

    Sorry I haven't been back., super busy this morning.

    Gina, I am not sure I can easily send something?

    This is frontend db, and everything relies on the backend, and they are both pretty loaded.

    Very busy day, will see what I can do.

    Al, I had downloaded yours, but was honestly overwhelmed.

    I have found things and made tweaks to make them work for me, by figuring little things out, but I don't begin to understand what yours is doing or why.

    I will download your newest version, and look again, the more I look at things, the more I begin to understand.

    S.A., will download the newer version you made available and try again.

    Will be later tonight, probably from home.

    I really do appreciate you all taking your time to help me!

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-06-18T21:30:02+00:00

    Sorry, try this version. I uploaded the wrong one earlier.

    https://1drv.ms/u/s!AvDZzWjSDmROqG5P3YOXgKrGl7w4?e=6CBOgX

    Saved to my drive, enabled content, no errors, opens an Explorer window displaying file names like search box.

    Let me look through the code a little and see if I can decipher what it is doing.

    One question though, shouldn't what is showing in the window also be displayed in the 'Results:' boxes below the search boxes?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-06-18T22:56:39+00:00

    SUCCESS!!!!!!

    To DP's code I made these changes to the Function:

    Function FF_ListFilesInDir(sPath As String, sSearchTerm As String, Optional sFilter As String = "*") As Variant

    'added sSearchTerm as additional argument

    Dim aFiles()              As String

    Dim sFile                 As String

    Dim i                     As Long

    On Error GoTo Error_Handler

    If Right(sPath, 1) <> "" Then sPath = sPath & ""

    sFile = Dir(sPath & sSearchTerm& "*." & sFilter)

    'added & sSearchTerm as additional argument

    I made these changes to the Sub:

    Private Sub Form_Open(Cancel As Integer)

    Dim aFiles()              As String

    Dim sFile                 As Variant

    Dim sSearchTerm           As String 'added this to get the SKU from the form

    Dim i                     As Long

    Const sPath = "\FOLDERS\Eng_Dept\All_Staff\Engineering\Database Requirements\ExportReports\Archive BOM Copies"      'my Path to extract a list of files from

     On Error GoTo Error_Handler

    Me.Lst_Files.RowSource = ""

    sSearchTerm = Me.txtStockCode    'added this to get the SKU from the form

    aFiles = FF_ListFilesInDir(sPath, sSearchTerm, "HTM")   'added sSearchTerm to get the SKU from the form

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-06-18T23:28:15+00:00

    Sorry, try this version. I uploaded the wrong one earlier.

    https://1drv.ms/u/s!AvDZzWjSDmROqG5P3YOXgKrGl7w4?e=6CBOgX

    Thank you for sharing this, looking at this gave me some ideas for things to try!

    After looking at what I had changed, then looking at yours again, I made some modifications.

    In your Subs(?), you had these:

    Public Sub LetFindSomeFiles(ByVal sSearchTerm As String, ByVal sSearchLocation As String, lst As Access.Control, lSearchType As Long, lSearchDepth As Long)

    Call LetFindSomeFiles(Me.txt_SearchTerm, Me.txt_SearchLocation, Me.lst_Results, Me.frm_Search, Me.frm_folders)

    You Function has five arguments, and your Function Call answers those five arguments, but in your Call they are not variables, they are explicit locations (textboxes).

    Modified what the Sub by deleting these

    Dim sSearchTerm           As String

    sSearchTerm = Me.txtStockCode

    And changed this to:

     aFiles = FF_ListFilesInDir(sPath, Me.txtStockCode, "HTM")

    From:

    aFiles = FF_ListFilesInDir(sPath, sSearchTerm, "HTM")

    Everything still works!

    As an aside, I notice a lot of the code variables(?) are things like 'sFile' and 'sSearchTerm'.

    Are these protected or reserved terms or just and arbitrary name the writers use?

    Is there significance in using a letter at the beginning of the name?

    Was this answer helpful?

    0 comments