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-18T05:37:55+00:00

    >>> Not my question Al but if it were, I'd be using either your app or Daniels, both work well and seems to me do just what the op requires.

    Thanks, S.A. Marshall! Wow, you and Gina have really worked tirelessly on this. I apologize on being on the sidelines. My clinic has been swamped with this nasty Coronavirus epidemic. Today's my day off.

    >>> They both work as designed, but I prefer Daniels version as it only displays file names.My files are always in the same folder, so I don't need to see the path.

    I understand, Kevin. I may be biased, but I still think that my technique is the best.

    But you’re right. You asked for only names of documents, so I decided to update this applet.

    I still like displaying the full hyperlinks in activated blue color, but I could see how somebody could find it ugly and prefer just the file name without the “hyperlink” look. So I added a couple of new forms. I also updated the code a bit. Hopefully, you can apply the underlying code to your project.

    BTW, if you wish to make the folder location static, just make the clickbutton "Get New Folder" and "Folder Location" textbox invisible.

    • The initial form looks like this now-

    • If you select the "URL Hyperlink Listbox" you'll still get the "old" form-

    • You might like this one, though. Just click on "Document Names Listbox"-

    When you double click on any of the form names, you activate the following code (quite simple, really):

    On Error Resume Next

    Xx = List6.Column(2)

    Application.FollowHyperlink Xx, , True

    Exit Sub

    The Xx = List6.Column(2) calls up the Linkcolumn URL.

    One small error that the applet had, but didn't affect functionality was that the "D ocName" column really showed only the URL locations. Now the "FLocation" column has the URL locations and the "DocName" has... the document names:

    Anyhow, it's up. The URL link is here.

    My OneDrive folder at home isn't synching, so I had to reupload the latest file from my office computer. Here's the URL.

    Hopefully, it'll help you...

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-06-18T04:31:55+00:00

    No need to wait on the side Gina. The sample I sent is one Daniel put together, not something I've copy and pasted or modified. It works fine here but seems to be a problem on the op's end (you might like to try it yourself). I think what's needed is at least a version that works on the op's end. Seems he got a result with the version he is working on but we don't have a copy. If he can send you a copy to you or upload one then some progress might be made.

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

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-06-18T02:16:22+00:00

    Okay, well I see you got a sample, so I'll just wait over on the side.

    Side note, I would make sure the file is in a Trusted Location which is not on your Desktop and then test again.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-06-18T01:54:27+00:00

    I saved a copy of a file to my desktop - C:\Users\kevinr\Desktop.

    Navigated there with the button, same message boxed as before.

    ' Windows cannot find (null)… '

    Was this answer helpful?

    0 comments