Hyperlink Variable File name with path in columns

arcaidius

Board Regular
Joined
Dec 4, 2018
Messages
97
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.

VBA Code:
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top