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: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-06-15T19:58:38+00:00

    Okay, what are you getting?

    Perhaps...

    sFile = Dir(sPath & """ & [ txtStockCode ] & """ & "*." & sFilter)

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-06-15T17:55:09+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.

    I am not sure I am asking the right question?

    This lists ALL files in the specified folder in a listbox:

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

    This only lists the files in the specified folder in a listbox that names start with 98500178:    sFile = Dir(sPath & "98500178" & "*." & sFilter)

    What I am wanting is only files in the specified folder that names start with what is in a textbox on the form.

    The textbox is named ' txtStockCode '.

    So something like:

    sFile = Dir(sPath & "[ txtStockCode ]" & "*." & sFilter)

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-06-15T16:26:28+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.

    I replaced Debug.Print sFile with Debug.Print sPath.

    This is the Immediate Window:

    *\FOLDERS\Eng_Dept\All_Staff\Engineering\Database Requirements\ExportReports\Archive BOM Copies*

    I moved Debug.Print sFile below Loop and thought it wasn't returning anything, but realize that it is adding space(?) to the immediate window; I reopened the form four times and in the immediate window the cursor was four rows down, although there

    wasn't anything in the rows.

    I moved Debug.Print sFile into Loop:

    Do While sFile <> vbNullString        If sFile <> "." And sFile <> ".." Then            ReDim Preserve aFiles(i)            aFiles(i) = sFile            i = i + 1        End If        sFile = Dir     'Loop through the next file that was found        Debug.Print sFile    LoopIt returns a lot!

    There are ~2300 files in the folder and it returns the names of the last 198 (I only copied and pasted the last four):

    99800604A FLUSH WOOD LRG - RECT (FLR) (AUS) BOM 07-22-2019.htm

    99800604A FLUSH WOOD LRG - RECT (FLR) (AUS) BOM 10-30-2019.htm

    99800606 FLUSH WOOD LRG - RECT (FLR) NEXT GEN BOM 04-20-2020.htm

    99800606 FLUSH WOOD LRG - RECT (FLR) NEXT GEN BOM 04-21-2020.htm

    PS; this is all with this:

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

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-06-14T08:48:19+00:00

    You may wish to try my applet at https://1drv.ms/u/s!Am9xB7i5mNRolfp5rL72l2DH1x6jZg?e=5RCsAB

    1. You can assign a default folder location or use the Windows shell to choose a new location:

          2.  You can filter the files (in your case, type in "SKU"):

         3.  All files are hyperlinks, as you wish to have.

    Play with it.  I can go into it further if you find that the code's helpful.

    Was this answer helpful?

    0 comments