Help with Macro to find full path of Folder, using input of first 4 chars of folder name

I could use a bit of help with a code I'm working on.
Essentially, I need to open a file, and I know where it is.
Unfortunately, I prefer not to remember ten thousand file folder names or to have to manually enter them into this code.
I know where the file is located and how to get to the directory where they are located. No problem.

What I need is, when I manually input the job number, for example "1234", it will search for the actual folder which is named "1234 - Tree House Lane Remodel".

Please Login or Register  to view this content.

Thank you very much and happy holidays.

Edit: I forgot to add, the file will be something like "THL - BOMs.xls" or "THL - BOMs.xlsm" where "* - BOMs.xls*" is the same.

Use this as a starting point

Please Login or Register  to view this content.

This isn't precisely what I need.
Basically, I just need to open a file and I know its location, but what I don't know is the full name of one folder.
For example, I want to lookup a specific job number "1234", and the full job name would be called "1234 - Tree House Lane Remodel".
The full path would be "P:/1000 Series Jobs/1234 - Tree House Lane Remodel/Engineering/Dwgs/THL - BOMs.xmls".
There are thousands of these files, and I'd like to look them up simply by entering the job number "1234".
I can figure out everything except for a type of wildcard for the folder "/1234 - Tree House Lane Remodel/".
I've tried " & JobNo & " - *" but wildcards do not work when looking up directories.

Your code can certainly give me a file name that I don't know entirely, however, I can't seem to get the directory.

Edit:
I just figured out how to find a full path based on some variables. Apparently my syntax was a bit off and had to fiddle with it to work.
Here's my code if anyone's interested.

Please Login or Register  to view this content.