Hyperlink Variable File name with path in columns

[arcaidius]

Hello, I am trying to hyperlink pdf files in each row that will have a variable in the filename, or may not exist yet.

I have the exact file path in column K for each row. The file name will be in Column H plus specific Text and then may or may not have additional characters.
I put the Specific Text in Column A.
I got this code from previous posts and I think its almost what will work, I just can't figure out how to change the File path to Column K for each row. A little help please? I'm learning.

Option Explicit
Sub CreateHyperlink()

Dim lRow As Long
Dim Path As String
Dim File As String
Dim Ext As String
Dim r As Range

lRow = Cells(Rows.Count, "A").End(xlUp).Row
Ext = "*.pdf"
Path = "C:\Users\YourPath\"

' Go through each cell in column D starting at 2
For Each r In Range("A2:A" & lRow)
    File = Dir(Path & Ext)
    If File Like "*" & r.Value & "*" Then
        r.Hyperlinks.Add r, Path & File, , , r.Value
    End If
    File = Dir()
Next r
End Sub

 

[GWteB]

The code you provided searches for file names on disk that match data in column A.
It seems to me that what you're after is something different, although it's not clear to me.
What is meant by:
- Specific Text?
- exact file path; only folder name or does it include a file name?
- file name with or without "additional characters"?
If you are able to be more specific, preferably with an example, that would be helpful.

 

[arcaidius]

The code you provided searches for file names on disk that match data in column A.
It seems to me that what you're after is something different, although it's not clear to me.
What is meant by:
- Specific Text?
- exact file path; only folder name or does it include a file name?
- file name with or without "additional characters"?
If you are able to be more specific, preferably with an example, that would be helpful.

Sorry,

thanks for replying, been busy with holidays.

I can extract the exact folder location, but the actual file name will be slightly different every time because someone else is naming it. the file will always have the word "Inspection" though.

 

[GWteB]

Still, you're not answering my questions. Furthermore, what should be done if multiple files are found on disk that match the condition? Would recommend to post an example of your worksheet using xl2bb and to indicate where hyperlink(s) should be placed.