Open folder location based on partial information

[Soper87]

Hi,

I am able to create easily enough to open or even hyperlink to a specific folder location based on cell values but would like to be able to open a location based on only cell value. To give a background example of what I am trying to achieve:

Cell A1 = Name of Client (NAME)
Cell B1 = Unique ID number (ID)

The server directory is structured that folders are named as NAME ID (i.e. Smith 01234). I can open the appropriate folder based on both values being present. How is best to do this so that you can open the folder based only on the contents of B1. In theory, the name of client may change but the Unique ID never will so it is planned to be able to open the folder on the server based on that value but keeping the naming convention on the server as NAME ID

Hopefully above makes sense, one of those attempts where I have tried and come close but seem to be missing something for it work correctly

Thanks in advance

 

[Johnny C]

It depends on what happens if someone changes their name. Will there be 2 folders with that number? If so you're a bit stuffed though you could choose the one with the latest creation date.

You need to use VBA to do it.

Public Function ClientDirectory(ClientID as String) as String

ClientDirectory = ""
MyPath = "c:\"   ' Set the path.
MyName = Dir(MyPath, vbDirectory)   ' Retrieve the first entry.
Do While MyName <> ""   ' Start the loop.
      ' Use bitwise comparison to make sure MyName is a directory. 
      If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory Then 
          If MyName Like "*" & ClientID  & "*" Then ClientDirectory = MyName
      End If   
   MyName = Dir()   ' Get next entry.
Loop

End Function

to use this, copy this into the worksheet code or personal.xlsb. Amend the MyPath to be the root folder where all the client folders are stored.
On the worksheet, insert a User-Defined function and look for ClientDirectory and select the cells like B1. You can copy the function down if you've a list, and it should return the folders you need.

You cold automate the hyperlink creation too, it goes something like this

Sub AutoHyperLink
ActiveSheet.Hyperlinks.Add Anchor:=(Cell where you want the hyperlink), Address:="", SubAddress:="'" & linkID & "'!A1", TextToDisplay:=linkID
End Sub

you'd need to play around with that I think Address or Sub address would be your folder, LinkID would be the filename, you'd need to look that up in Help.

 

[Soper87]

Thanks for that, will give it a try. It may be that the name changes on the folder but the Unique ID will never change so ideally can search in a predefined directory to open the folder containing that unique ID