Hyperlink to first 3 characters in folder name

[Kiloelectronvolt]

Hi All!

I'm looking to do a hyperlink that will pull data from the cell and find the appropriate folder.

Column A will have 5 digits numbers in it. IE (28102) and the windows folder would be M:\Project Folder\281-VARIABLE JOB NAME

So I would like to click on 28102 and have it take me to the windows folder that starts with prefix 281. (first 3 characters)
If I click on 27105, it would take me to the folder with prefix 271.

Thank you!!!!

 

[Micron]

I don't think you can use wildcards or partial folder paths for this. AFAIK, the dialog will open to the last folder that it can make sense of, assuming the code doesn't raise an error.
My approach would have the parent folder "281" with the variables as sub folders, or in code, string together the initial path & 281-variable and open that folder.
Or did I misunderstand your folder naming convention?

 

[Kiloelectronvolt]

I don't think you can use wildcards or partial folder paths for this. AFAIK, the dialog will open to the last folder that it can make sense of, assuming the code doesn't raise an error.
My approach would have the parent folder "281" with the variables as sub folders.

Honestly that's what I think I have to do too. I know you can use the formula LEFT3 in a cell, but didn't know if you could incorporate in into folders.

We'll probably have to restructure the folder systems to accommodate then . Thank you!

 

[Micron]

I tried it and all it did was pick the last folder it could make sense out of.
Can't you get Left(column A,3) and concatenate that & "M:\Project Folder\" & "[job name on the sheet]"?

 

[Kiloelectronvolt]

I tried it and all it did was pick the last folder it could make sense out of.
Can't you get Left(column A,3) and concatenate that & "M:\Project Folder\" & "[job name on the sheet]"?

Thank you for trying.

I actually have that suggestion in place on another spreadsheet and that's one solution. I had trouble getting people to name the folders correctly, so I was curious if this could work.

The issue we have is our jobs are addresses, and staff will say 1234 N. Fake Street, and then name the folder 1234 North Fake St. --- I will have to have them focus a little more on standardization if I want the hyperlinks to work.

 

[Micron]

Don't allow them to create the folder from Windows Explorer, use vba instead? I guess that would only be a policy, but shouldn't be hard to implement. Then again, what's to stop the invalid data from being in the sheet rather than the folder? If that can happen, any code solution related to the first post will fail to produce the desired result in those cases.

Just a guess, but I suspect whatever you're doing would be better of being handled by a database (as long as it was properly built).