VBA to Search for files in a Folder and Opens it

I have a spreadsheet and the content has different file names that are arranged in a single Column. For example, in Column A, I have the following file names.

A1: Math2.xlsx
A2: Math1.xlsx
A3: Eng1.xlsx

These files are located in the directory C:/Projects/Sample

But the problem is this: Under the Sample folder, I have so many other folders created there (about 10). Hence I don’t know exactly the location of the files listed above in the Sub Folders.

I need an excel macro that can search for the files listed from A1 to A (whatever) and show me where it is exactly located under the Sample Sub-Folders and open it when I click on it. For example in A1 if I click on Math2.xlsx, it will show me the sub folder (Under Sample Folder) where it is located and opens it up.
Is this possible?

Thanks in advance so much for your time.

Hello vnzerem,

Add a new VBA module to your workbook. Copy and paste the code below into it. Select the sheet with the file names and run the macro "LinkFiles". This will search the parent folder and all sub folders and their folders for the file name. When found, a hyperlink will be added to the cell. Hover the mouse over a file name on the worksheet and you will see the full path to file. Click the cell and it will open the file.

Function FindMyFile(ByVal ParentFolder As String, ByVal FileName As String, Optional FoundFile As String)

    Dim colFolders As New Collection
    Dim FolderItem As Variant
    
        ParentFolder = IIf(Right(ParentFolder, 1) <> "\", ParentFolder & "\", ParentFolder)
        
        FolderItem = Dir(ParentFolder, vbDirectory + vbHidden)
        
            Do
                If (GetAttr(ParentFolder & FolderItem) And vbDirectory) <> 0 Then
                   If FolderItem <> "." And FolderItem <> ".." Then
                       colFolders.Add ParentFolder & FolderItem
                   End If
                End If
               
                FolderItem = Dir()
                
                If FolderItem = "" Then Exit Do
                
                If LCase(FolderItem) = LCase(FileName) Then
                    FoundFile = ParentFolder & FolderItem
                End If
            Loop
            
            If Not colFolders Is Nothing Then
                For Each FolderItem In colFolders
                    Call FindMyFile(FolderItem, FileName, FoundFile)
                Next FolderItem
            End If
        
        FindMyFile = FoundFile
        
End Function

Sub LinkFiles()

    Dim Cell         As Range
    Dim FilePath     As String
    Dim ParentFolder As String
    Dim Rng          As Range
    Dim RngEnd       As Range
    Dim Wks          As Worksheet
    
        ParentFolder = "C:\Projects\Sample"
        
        Set Wks = ActiveSheet
        
        Set Rng = Wks.Range("A1")
        
            Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
            If RngEnd.Row < Rng.Row Then Exit Sub
            
            For Each Cell In Wks.Range(Rng, RngEnd)
                FilePath = FindMyFile(ParentFolder, Cell)
                
                If Cell.Hyperlinks.Count <> 0 Then Cell.Hyperlinks(1).Delete
                
                If FilePath <> "" Then
                    Cell.Hyperlinks.Add Anchor:=Cell, Address:=FilePath, TextToDisplay:=Cell.Text
                End If
            Next Cell
        
End Sub

Hello vnzerem,

You're welcome. That was interesting and fun project to do.

Hello vnzerem,

Recursive functions like FindMyFile are powerful programming tools. The draw backs are they can difficult to implement and understand. Glad you like it.

Hello vnzerem,

Glad to see you were able to solve your problem. If you have any other questions, let me know.