Selecting first file from multiple Dir() matches

[ahartman]

Hi all. I am working on a bit of code that will take the current cell's value and search through some known directories for a file that matches. The cell value would be something like 12-A-1234-01, and the code takes that value and parses it into some likely file names, then searches expected directories for those filenames. If it finds one that's a match, it calls a viewing program for the matched file. Match categories are divided into 3 groups:

1) Exact match - the file 12-A-1234-01.plt exists and this is a simple, solved case.
2) Short match - the file 12-A-1234.plt exists. This is a slightly more tricky case, as I have to check a variation on the cell's value, but not a problem. Also a solved case.
3) Multiple match - the files 12-A-1234-01_1.plt, 12-A-1234-01_2.plt, etc... exists. This is a case where more than one file is related to the matched cell's value. For simplicity, I want to just take the first one listed in the directory, and alert the user that other's may be available. Users aren't very rigorous in their filenames, and so I can't always count on the multiples being *_1 formats, they may be *_1A or *_A or *_01, etc, and I don't want to spend time looking for an infinite number of variations.

My code for this whole endeavor is below (excuse some debugging MsgBoxes):

Please Login or Register  to view this content.

My tactic was to search with the * character to see if multiple matches were available. Hence the "cfPLT_mult = .... "*.plt" bit of code near the top. However, doing Dir() with this path returns nothing. What am I missing here?

[Leith Ross]

Hello ahartman,

I wrote a macro using a powerful and versatile pattern matching construct called Regular Expressions. This has greatly reduced the code need to perform you file matching. I have also included a Dir loop to examine all the matching files in the folder. The ParseText macro is the same.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[ahartman]

Leith,

This looks mighty impressive! I was browsing for answers and found some stuff on the Like operator, and it used the same sort of pattern matching stuff as you created. I was wondering if this might be a good solution. There may be other formats that I'll have to check, but I think I can disect your code and figure out how to make modifications for other situations. I'm pulled away onto another task atm, but I'll try to check your code in my application and give you feedback. Thanks for your help!

[Leith Ross]

Hello ahartman,

Here is link to Microsoft about Regular Expressions. It should help you understand and expand the pattern if you need to.

Regular Expressions

This is much more powerful than simply using Like. If you have any questions, ask.

Sincerely,
Leith Ross