VBA to Auto Hyperlink a .pdf file from a folder based on cell value

djones42

New Member
Joined
Aug 10, 2016
Messages
9
I'm looking for some code or some detailed help as I have really little to no experience with visual basic.

So here's what I'm trying to achieve:

I have a spreadsheet of a test log which features test numbers in column A, and other important info such as test date, description etc. in the following columns. These test numbers are all 4 digit numbers, "1234." There's really no finite end to the amount of test numbers, the list is always growing.

All of these test numbers have a scanned PDF of the test file in a certain folder. The PDF filenames are either the exact test number or the test number with some additional test details, so either "1234" or "1234 example test info."

I want to automatically add hyperlinks of these PDF's to either one cell at a time or all cells in column A. However, since some filenames are not exact matches to the cell's in column A, the code should ignore the extra text. I feel in would maybe make the most sense if the code could some how only look at the first 4 digits of the filename as that is always going to be the test number so would match what value was in the cell(s).
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Try this, if you are not aware of where to use this or how to execute it let me know, will try to help

Code:
Sub AddHypaerlinks()

Dim lastRow As Long
Dim myPath As String, fileName As String


myPath = "C:\Users\dchaney\Documents\" 'SET TO WHERE THE FILES ARE LOCATED
lastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lastRow

    fileName = myPath & Range("A" & i).Value & "*.pdf"
    
    If Len(Dir(fileName)) <> 0 Then 'IF THE FILE EXISTS THEN
    
        ActiveSheet.Hyperlinks.Add Range("A" & i), myPath & Dir(fileName)
    
    End If
    
Next

End Sub
 
Upvote 0
Well this seems to be doing exactly what I wanted it to do, thank you! I had managed to scrounge up some code from some other threads and sites and pieced something together, but this absolutely works better.

Thanks again, dchaney.
 
Upvote 0
OK, so I have one more question actually, and I swear I fiddled around and got it to work yesterday afternoon, but I can't replicate it now.

If I want to link a folder in the same manner, rather than a file, is it as simple as replacing "*.pdf" or something else entirely?

Thanks.
 
Upvote 0
If you want to link to a Folder and not a file then use the following... Highlighted in red are the changes made

Code:
Sub AddHypaerlinks()

Dim lastRow As Long
Dim myPath As String[B][COLOR=#ff0000] ', fileName As String - [/COLOR][COLOR=#FF0000]Comment Out, no longer required for folder link[/COLOR][/B]

myPath = "C:\Users\dchaney\Documents\" 'SET TO WHERE THE FILES ARE LOCATED
lastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lastRow

[B][COLOR=#ff0000]   'fileName = myPath & Range("A" & i).Value & "*.pdf" - Comment Out, no longer required for folder link[/COLOR][/B]
    
    If Len(Dir([B][COLOR=#ff0000]myPath[/COLOR][/B])) <> 0 Then 'IF THE FILE EXISTS THEN [B][COLOR=#ff0000](Changed from myFile to myPath for Folder Link)[/COLOR][/B]
    
        ActiveSheet.Hyperlinks.Add Range("A" & i), myPath [B][COLOR=#ff0000]'& Dir(fileName) [/COLOR][COLOR=#FF0000] - Comment Out, no longer required for folder link[/COLOR][/B]
    
    End If
    
Next

End Sub
 
Last edited:
Upvote 0
With that I'm getting all my entries hyperlinked to the directory I set as "myPath."

Basically I have a folder in the directory where all my PDF's are that contains more folders with multiple PDF's in those folders that are assigned to one test number.

So I'm looking to take that folder, say "C:\users\dchaney\documents\folder\" and hyperlink all the subfolders located in "\folder\". This way the user can open the folder containing the PDF's they want, without me having to manually adjust a bunch of filenames or change my spreadsheet layout. Again this all works the same way where the subfolders names are "1234" or start with "1234..." Does that make sense?
 
Upvote 0
For the time being I managed to "link" to the folders by creating shortcuts to all my subfolders and changed the search for pdf's to the shortcut files ("*.pdf" to "*.lnk"). It's a little cluttered but at least the spreadsheet is working how I wanted it to. Still if there's another option where I don't have to come up with a workaround like creating shortcuts to the folders I would be very interested in learning it.
 
Upvote 0
Here you are, try this... The first thing it will do is see if there is a Sub Folder with the name used in the Excel cell... for instance if you have a folder named 1234 it will link it to that... if you do not have a folder named 1234 but you have a file, it will link to that file... if both are present it will link to folder, not file...

Code:
Sub AddHypaerlinks()

Dim i As Integer
Dim lastRow As Long
Dim myPath As String, fileName As String, subFolder As String

myPath = "C:\Users\dchaney\Documents\" 'SET TO WHERE THE FILES ARE LOCATED
lastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lastRow

   fileName = myPath & Range("A" & i).Value & "*.pdf"
   subFolder = myPath & Range("A" & i).Value & "\"
    
    If Len(Dir(subFolder)) <> 0 Then
    
        ActiveSheet.Hyperlinks.Add Range("A" & i), subFolder
        
    ElseIf Len(Dir(fileName)) <> 0 Then
    
        ActiveSheet.Hyperlinks.Add Range("A" & i), myPath & Dir(fileName)
    
    End If
    
Next

End Sub
 
Upvote 0
OK, so that's working, but it only works if the cell value is the exact same name as the folder name. I'm not sure if there's a workaround for that or if it has to be the exact same name when it comes to folders. If that is the case that's not a huge issue I can change the folder names. So I get the hyperlink if my cell is "1234" and my folder is "1234" but not if my cell is "1234" and my folder is "1234 pressure test" for example. And like I said if I'm not able to have it recognize "1234 pressure test" as the folder I want for my cell value of "1234" when there's no other sub-folder that starts with "1234..." then It's not a huge deal for me to change my sub-folder names. And thanks again for all your help, I really appreciate it.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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