Hello, Im using a small macro that searches for a file in a particular folder. It searches for the Drive letter (as this can change) and uses the active cell as the file name, see below:
Sub FindDoc()
    Dim fso
    Dim file As String
     
   On Error Resume Next
     
    Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.FileExists(Left(ActiveWorkbook.FullName, 2) & "\Folder Name\Folder Name\Folder Name\Folder Name\" & ActiveCell.Value & ".pdf") Then
        ActiveWorkbook.FollowHyperlink Left(ActiveWorkbook.FullName, 2) & "\Folder Name\Folder Name\Folder Name\Folder Name\" & ActiveCell.Value & ".pdf"
    Else
    MsgBox ("No File Found."), , "PDF SEARCH"
    End If
     
       On Error GoTo 0

End Sub
The problem I have is it turns out that there could be more than file with the same name, just numbered in order e.g. 1 to 10. Could someone please help me create a loop so it checks for the file name+number untill it finds the latest file? Or is there someway of opening the file that contains the active cell as part of the file name, but opens the newest one based on date?
Im not very good with loops, have tried but failed misserably!
All help much appreciated.
Thanks