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-17T18:44:10+00:00

    SKUNum as a String is correct but you must tell the Function what SKUNum is.  So you are saying Me.txtStockCode errors.  Are you sure that is the name of the control on your Form?  Because you have to tell it SKUNum = Me.txtStockCode or you will get nothing.

    And why are you declaring the Function as a Variant?  It's a String.

    In the Function if I replace:

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

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

    I get the 'Invalid use of Me keyword' compile error.

    When in error/debugger mode, in my Form Open Sub, if I hover over SKUNum = Me.txtStockCodeit tells me it is seeing what is in the textbox:

    https://learn-attachment.microsoft.com/api/attachments/3b4183ce-fd88-43e8-b14a-3157f8239c2d?platform=QnA

    I assume that since the path (sPath ) is being defined in in the Sub that the textbox variable (SKUNum) does also.

    So where/how do I pass this to the Function?

    Declaring as a Variant is how the original code is, or are you referring to something else?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-06-17T19:45:46+00:00

    What about

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

    ...does that work?

    And yes, long thread so ignore the whole Variant lines.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-06-17T20:59:50+00:00

    What about

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

    ...does that work?

    I tried that.

    Changed:

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

    To:

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

    It errored and highlighted the original line if I didn't add SKUNum As String,.

    Also changed:

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

    To:

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

    !!EDIT!! This works in that it doesn't cause an error, but the listbox is empty.

    There is one empty row in the immediate window.

    I feel that it is ssoooo close, and some silly little thing is not right?!?

    If I have not said it enough, I really appreciate all of you taking your time to look at this and try to steer me in the right direction!

    Would it help if I posted the entire code as I have it?

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-06-17T21:26:40+00:00

    Function FF_ListFilesInDir(sPath As String, SKUNum As String, Optional sFilter As String = "*") As Variant sFile = Dir(sPath & SKUNum& "*." & sFilter)

    There is one empty row in the immediate window.

    If I leave ***SKUNum As String,***in place, but remove & SKUNum :

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

    In the immediate window it lists the last ~200 file names and that includes some shortcuts I have in that folder to other folders.

    In the Form Open Sub there is this line:

        aFiles = FF_ListFilesInDir(sPath, "HTM")

    Since the number of arguments in Function is different than what is being called for in sFile , it is disregarding "HTM" in the Sub?

    Does that offer any clues?

    Was this answer helpful?

    0 comments