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

[djones42]

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

 

[dchaney]

I am not sure if it will work or not, but try to change

[COLOR=#333333]subFolder = myPath & Range("A" & i).Value & "\"[/COLOR]

to

[COLOR=#333333]subFolder = myPath & Range("A" & i).Value & "[/COLOR][COLOR=#ff0000][B]*[/B][/COLOR][COLOR=#333333]\"[/COLOR]

See if that allows it

 

[djones42]

I had already given that a go actually and I guess it reads it wit the asterisk as part of the directory/folder name and gives me an error. If you have any other epiphanies on how to make that work feel free to let me know otherwise I'd say don't worry about it, you've certainly helped me out enough.

 

[human24]

hello!
I am searching this type of code for days now. and its working fine. My problem is I have multiple duplicate files and what I need to be linked is the biggest file. pls help.

 

[dchaney]

How can you have multiple duplicate files? The file names must have some sort of different identifier as you can not save a file with the same name as a currently saved file...

 

[fowzan]

Try this, if you are not aware of where to use this or how to execute it let me know, will try to help

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

i have a very similar requirement. i tried this and it almost works for me aswell.
but i have a few issues
1. my file names contain "." instead of "/" as widows doesnt allow / to be in a file name.
ex: file name INV.17.163 and cell content INV/17/163
2. empty cells are linked to a random file.

can you help me modifying the code so it fits my purpose

 

[fowzan]

can someone help me with the code

 

[ahmed ramzy attia]

the below code is good but i need one for exact cell value for example pdf file 1000 in this code come to cell va;ue 1 and 10 and 100 and 1000 i need code to cell value 1000 only

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

[/QUOTE]

 

[llleo]

@dchaney

This code works great!!
Can you please let me know what coding language is used in here??

Also I have only one thing to clarify or customize in this formula that say if I have files named abc, abc Rev.1 and abc Rev.2

What file will be picked in the the excel-cell with file name abc???
I would actually like to link the excel-cell to the last revision i.e. abc rev.2 in this case. Similarly I have several files with more than 1 revisions. Is it possible to implement in this formula.

I would highly appreciate a response to this.

Many thanks!

 

[Marpsie]

Hi there, I know a lot of time has passed since this was initially posted but it seems to fit my need very closely. I copied your code and pasted it in VB but when I try to run the code, nothing happens. No window, no message, nothing.
What am I missing? Please advise.
Thank you!

 

[llleo]

Hi there, I know a lot of time has passed since this was initially posted but it seems to fit my need very closely. I copied your code and pasted it in VB but when I try to run the code, nothing happens. No window, no message, nothing.
What am I missing? Please advise.
Thank you!

Did you copy the directory and file locations where it says in green text?