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-12T18:23:01+00:00

    What is SKUNum?  And yes, you Dim if you are using a variable.  That said, under the for sFile please put...

    In the Function I did this:

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

    Dim aFiles()              As String

    Dim sFile                 As String

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

    Dim i                     As Long

    On Error GoTo Error_Handler

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

    sFile = Dir(sPath & SKUNum & "*." & sFilter) 'added & SKUNum to try to get the SKU from the form

    Debug.Print sFile

    In the Sub I did this:

    Private Sub Form_Open(Cancel As Integer)

    Dim aFiles()              As String

    Dim sFile                 As Variant

    Dim SKUNum        As Variant 'added this to try 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"       'Path to extract a list of files from

    On Error GoTo Error_Handler

    Me.Lst_Files.RowSource = ""

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

    aFiles = FF_ListFilesInDir(sPath, "HTM")

    This works, no errors, but it is still returning all files in the folder.

    If in the Function I change this: 

    sFile = Dir(sPath & SKUNum & "*." & sFilter) 'added & SKUNum to try to get the SKU from the form

    To this:

    sFile = Dir(sPath & "98500178" & "*." & sFilter)'added & SKUNum to try to get the SKU from the form

    The list is all files named starting with 98500178.

    So I think I am looking in the right place, just don't know what to do?

    Again, I am not a programmer and am just throwing things at the wall hoping something sticks?

    I appreciate the help you are offering!

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-06-12T18:38:18+00:00

    Debug.Print sFile

    ...and then copy and paste what shows in the Immediate Window.

    So now that I have been opening and closing my form there is something going on!

    With this:

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

    Debug.Print sFile

    I get this:

    98500178 864 TV GSR2 40K BOM 01-24-2020.htm

    98500178 864 TV GSR2 40K BOM 01-24-2020.htm

    If I change it to this:

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

    It changes to this:

    716-00001 J-BOX, TYPE 1 (WSDOT - DIA. PLATE) BOM 03-09-2020.htm

    98500178 864 TV GSR2 40K BOM 01-24-2020.htm

    98500178 864 TV GSR2 40K BOM 01-24-2020.htm

    If I change it back to this:

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

    It changes to this:

        716-00001 J-BOX, TYPE 1 (WSDOT - DIA. PLATE) BOM 03-09-2020.htm

        716-00001 J-BOX, TYPE 1 (WSDOT - DIA. PLATE) BOM 03-09-2020.htm

        98500178 864 TV GSR2 40K BOM 01-24-2020.htm

        98500178 864 TV GSR2 40K BOM 01-24-2020.htm

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-06-13T00:07:21+00:00

    Hmm, move it down under *Loop*.  Please make sure you run the code, like open the Form so we can see what it returns.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-06-13T00:10:41+00:00

    So, still missing the complete path because it looks like *sPath* is missing.  Put...

    Debug.Print sPath

    ...let's see what path it pulls.

    Was this answer helpful?

    0 comments