Create a Hyperlink without knowing the full folder name

[kgkev]

I would like to open a folder from a hyperlink in a cell.

The folder is situated in
\\IH-FS\Data\Production\Safety\A7 COSHH SHEETS & SDS\

Cell B1:B200 contain the start of the folder name

B2 = "COSHH ASSESSMENT 0001"
B3 = "COSHH ASSESSMENT 0002"

However, the actual folders include the name of the product/chemical on the data sheet.

"COSHH ASSESSMENT 0001 - PROPANE"
"COSHH ASSESSMENT 0002 - LOCTITE 603"

is it possible to use a wild card to still open the folder without knowing the actual folder name?

I have tried...and failed with

=HYPERLINK("\\IH-FS\Data\Production\Safety\A7 COSHH SHEETS & SDS\"&B2&"*","LINK")

Any help would be appreciated.
I could rename all the folders to remove the product name, but we still need to be able to access the folders directly as well.

 

[sijpie]

you could write a function in VBA to get the full directory name, and then use that to create the hyperlink.

Option Explicit

Function GetSubDirectory(sParentDir As String, sStartDirName As String) As String
    Dim FSO As Object
    Dim objFolder As Object
    Dim objSubFolder As Object
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = FSO.GetFolder(sParentDir)
    
    For Each objSubFolder In objFolder.SubFolders
        If LCase(objSubFolder.Name) Like LCase(sStartDirName) & "*" Then
            GetSubDirectory = objSubFolder.Name
            Exit For
        End If
    Next objSubFolder
    
End Function

In a cell enter:

=GetSubDirectory("\\IH-FS\Data\Production\Safety\A7 COSHH SHEETS & SDS\",B2)

and it will give you the full path.
you can use that in your hyperlinnk