VBA code to search on folder and sub folder for file named in worksheet

Dear VBA express Members,
I have and excel file includes records which in column H as range H1 = 360001 H2 = 360002 H3 = 360003 ETC
I asked my clients to save a document in shared folder names with what mentioned above, as a copy of invoice for 360001 named 360001 and the same with the rest
all what i need to make the VBA give me a file path if it found the invoice in the folder.
the result should be shown in column I as a path and if i clicked it opens the invoice copy.
invoices come with extension PDF or tif
N.B the data numbers of invoices are variable which change every day so i need to check that i have a copy for each invoice and can review it.
many thanks.Attachment 30423

See Help on VBA Dir and Excel & VBA Hyperlink

you can create a Sub to check if the same invoice exists on the Shared folder
and create a Hyperlink on your worksheet if it does.

Code:

---

Private Sub t()
    'put the shared path here. dont forget to add the "backslash"
    Const THE_SHARED_PATH As String = "\\PC_NAME\SHARE_FOLDER\"
    Dim lngLastRow As Long, strFile As String, i As Long
    With Worksheets(1)
        lngLastRow = .Cells(1, 8).End(xlDown)
        If lngLastRow > 1 Then
            For i = 2 To lngLastRow
                strFile = Dir$(THE_SHARED_PATH & .Range("H" & i) & ".*")
                If Len(strFile) <> 0 Then
                    .Hyperlinks.Add Anchor:=.Range("I" & i), _
                    Address:=THE_SHARE_PATH & strFile, _
                    ScreenTip:="Invoice number " & .Range("H" & i), _
                    TextToDisplay:=strFile
                End If
            Next
        End If
    End With
End Sub

---

many thanks for your help,
now i can't add the file name, so i changed it to be on my own PC as "D:\loma New"
kindly if we can have quick meeting to show you what I'm facing.

Quote:

---

Originally Posted by Ahmed emad

kindly if we can have quick meeting to show you what I'm facing.

---

Attach a sample file to your post so we can see what you are facing.

Many thanks for your efforts and time.The code worked as forgot to add the last backslash. I noticed that the code takes a long time to get the result, but it gets the results, much appreciated.