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

[Ahmed emad]

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.30423

[SamT]

See Help on VBA Dir and Excel & VBA Hyperlink

[arnelgp]

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.


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

[Ahmed emad]

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.

[Aussiebear]

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.

[Ahmed emad]

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.