Search file and hyperlink

This is a versatile macro that allows you to nominate a directory (&/or subdirectory as well) and list all files and hyperlink them - in your case when inputbox asks for filename (which accepts wildcards * & #) simply click cell A1 - browse to your my docs directory and it will search - you will need to modify to your specific needs - if you need help modifying it let me know.

Sub test()
    Dim myDir As String, temp(), myList, myExtension As String
    Dim SearchSubFolders As Boolean, Rtn As Integer, msg As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show Then
            myDir = .SelectedItems(1)
        End If
    End With
    msg = "Enter File name and Extension" & vbLf & "following wild" & _
    " cards can be used" & vbLf & "* # ?"
    myExtension = Application.InputBox(msg)
    If (myExtension = "False") + (myExtension = "") Then Exit Sub
    Rtn = MsgBox("Include Sub Folders ?", vbYesNo)
    SearchSubFolders = Rtn = 6
    myList = SearchFiles(myDir, myExtension, 0, temp(), SearchSubFolders)
    If Not IsError(myList) Then
    For k = LBound(myList, 2) To UBound(myList, 2)
    Sheets(1).Cells(k, 1) = myList(1, k) & "\" & myList(2, k)
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(k, 1), Address:= _
         CStr(myList(1, k)) & "\" & CStr(myList(2, k)), TextToDisplay:=CStr(myList(1, k)) & "\" & CStr(myList(2, k))
    Next
        'Sheets(1).Cells(1).Resize(UBound(myList, 2), 2).Value = myList
        'Application.Transpose (myList)
    Else
        MsgBox "No file found"
    End If
End Sub
 
 
Private Function SearchFiles(myDir As String _
    , myFileName As String, n As Long, myList() _
    , Optional SearchSub As Boolean = False) As Variant
    Dim fso As Object, myFolder As Object, myFile As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    For Each myFile In fso.getfolder(myDir).Files
        Select Case myFile.Attributes
        Case 2, 4, 6, 34
        Case Else
            If (Not myFile.Name Like "~$*") _
            * (myFile.Path & "\" & myFile.Name <> ThisWorkbook.FullName) _
            * (UCase(myFile.Name) Like UCase(myFileName)) Then
                n = n + 1
                ReDim Preserve myList(1 To 2, 1 To n)
                myList(1, n) = myDir
                myList(2, n) = myFile.Name
            End If
        End Select
    Next
    If SearchSub Then
        For Each myFolder In fso.getfolder(myDir).subFolders
            SearchFiles = SearchFiles(myFolder.Path, myFileName, _
            n, myList, SearchSub)
        Next
    End If
    SearchFiles = IIf(n > 0, myList, CVErr(xlErrRef))
End Function

Do you mean that there will be directory names in Column A and the code is to search the directory name in Column A cells and then place all the files found in Column B and the Column B files are hyperlinked.
Could you perhaps just upload a sample worksheet just showing how you want the sheet to appear - I think I know what you mean however a sample worksheet would make it quick and easier if I knew exactly what you wanted. Just select Go Advanced, Manage Attachments then select the sample workbook to upload it to your post.

The code will first ask you to browse to a directory to search - the code will always search any subdirectories as well - if a file is found that matches like the files you have listed in Column C it will place a hyperlink to the file - if the file cannot be matched to any file in directory or subdirectories then "Not Found" is placed next to the file name. I do not change the names of the file in column C - I just leave it as is - this could be change so it displays the actual filename - see code in workbook for instructions how to make the files in column C to be changed to match the the exact filename. Let me know if you have any problems. There is a button on sheet to start the process.