Building the correct path for followhyperlink

Question

BIG PICTURE

1. Go through a list and create a tab for each item in the list (Working)
2. Create a hyperlink in the list that links to the associated worksheet (Working)
3. Create basic header information on each worksheet and hyperlink back to index sheet (Working)
4. Insert a button for each reference listed in a corresponding cell in the index sheet (working)
5. Add hyperlink to button click that opens pdf, doc, or docx file (Not working, work in progress)

CURRENT PROBLEM

Depending on the button name, the file will be stored in 1 of 3 directories. While the button name is a unique part of the filename, there may be to the file name and the extension can vary between doc and docx.

I have three button name formats

F-1010
F-0400-01
928

In the first case I can generate the exact full filename exactly as the files will all be F-1010.pdf format

In the second case the file name will start with button name and be followed by additional text and then a variation in word document extension: F-0400-01 abc def.doc or F-0400-01 abc def.docx

In the third case the file name will start with OPSS folled by sometimes some text followed by the button name followed by a bunch of text and end with .pdf: OPSS 928 abc.pdf or OPSS.MUNI 928 abc.pdf

I tried using wildcards in the string but that is not working.

Sub btnClick()

Dim btnName As String
Dim FPath As String
    
    'btnName = Application.Caller
    btnName = "F-0400-01" 'assigned name for testing purposes
        If Left(btnName, 1) = "F" Then
            If Num_Characters_In_String(btnName, "-") = 2 Then
                FPath = "P:\2019\1234 Folder\08. Working\Specifications\Section F" & btnName & "*.doc*"
            Else
                FPath = "P:\2019\1234 Folder\10. Construction\01. Tender\F\" & btnName & ".pdf"
            End If
        Else
            FPath = "P:\2019\1234 Folder\10. Construction\01. Tender\OPSS\OPSS*" & btnName & "*.pdf"
        End If
    
    ThisWorkbook.FollowHyperlink FPath
   
End Sub

Error for second and third case

I read this question and this question to get me where I am

QUESTION

How do I properly build the path? How do I open the various file types?

Answer 1

I found this question and used the function solution by Rex.

I had a problem at the end of the function though with the transpose part giving me an error of type mismatch. so I modified the code as follows:

Sub btnClick()

Dim btnName As String
Dim FPath As String
Dim basePath As String
Dim sLink As String

    basePath = "P:\2019\1234 Folder"
    'btnName = Application.Caller
    btnName = "180"
        If Left(btnName, 1) = "F" Then
            If Num_Characters_In_String(btnName, "-") = 2 Then
                FPath = basePath & "\08. Working\Specifications\Section F\"
            Else
                FPath = basePath & "\10. Construction\01. Tender\F\"
            End If
        Else
            FPath = basePath & "\10. Construction\01. Tender\OPSS\"
        End If
    sLink = fileName(FPath, "*" & btnName & "*", ".*")
    ThisWorkbook.FollowHyperlink sLink
   
End Sub

and the modified filename sub as follows:

Function fileName(path As String, sName As String, ext As String) As String

'path is Full path from root.  Can also use path = ActiveWorkbook.path & "\"
'sName is the string to search. ? and * are wildcards.  ? is for single char
'example sName = "book?" or sName ="March_*_2014*"
'ext is file extention ie .pdf .xlsm .xls? .j*

Dim file As Variant 'Store the next result of Dir
Dim fname() As String 'Dynamic Array for result set
ReDim fname(0 To 0)
Dim i As Integer ' Counter
i = 0

' Use dir to search and store first result
fname(i) = path & Dir(path & "\" & sName & ext)
i = i + 1

'Load next result
file = Dir

While file <> "" 'While a file is found store that file in the array
  ReDim Preserve fname(0 To i) As String
  fname(i) = path & file
  file = Dir
Wend

'fileName = Application.Transpose(fname) 'Print out array
fileName = fname(0)
End Function