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

 

[Marpsie]

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

Hi, thanks for the quick reply. Now when I opened the code this morning to verify that I had done as per your instructions I immediately noticed something that all my looking-at yesterday I never saw. I had missed one "" at the end of "mypath"!
That'll do it!

So now it's working like a charm!
Thank you again!

 

[Marpsie]

Hi again, I actually noticed a serious problem with my linkage. It does not look for exact matches, but if my cell lists "25070" as the part number, the code will attach PDF "25070A". (25070 does not exist) So I tested it and sure enough, if I name it "250" it will still attach "25070A". This is a problem, because lots of our PDFs will have 5 digits but have the same number with an added character. for Example, we'll have both "25075" as well as "25075W". The code should look at the exact match of the cell, but ignore only the last "r**.pdf". (R** stands for rev00 or rev01. Our pdfs are named "25075r01.pdf". Hope that all makes sense.

Here is my current code. Please advise how to change it so it looks at the exact filename rather than just if it starts with the same characters.

Sub AddHypaerlinks()


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




myPath = "\\RDS\SharedDocs\CAD\Production Files\PDF" 'SET TO WHERE THE FILES ARE LOCATED
lastRow = Range("A" & Rows.Count).End(xlUp).Row 'A is the column to apply macro


For i = 2 To lastRow


fileName = myPath & Range("A" & i).Value & "*r**.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

 

[sanash]

DJones could you please help me out to how to execute the code