Hyperlink Variable File name with path in columns

arcaidius

New Member
Joined
Dec 4, 2018
Messages
42
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Dec 4, 2018
Messages
42
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,652
Messages
5,626,091
Members
416,161
Latest member
David1966Lewis

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
Top