Automating Hyperlinks

[Clo]

Hi,
I am new to this posting thing, but I read over some of your postings and found very helpful hints. This place is really cool.

Here is my problem. I have a excel spreadsheet that contains a column with numeric and text references. In a separate directory I have the same reference as pdf files. I would like to hyperlink this pdf files to the corresponding reference in my Excel sheet.

Right now I manually select each individual reference and hyperlink it to the pdf file. This is time consuming for over 300 entries. Any suggestions on how to do this quicker?

Any help would be greatly appreciated,

Best regards,
Clo

 

[Norie]

Why not use the HYPERLINK worksheet function?

 

[bbrnx19]

Clo
Try this out

Sub test()
Dim RN As Range, j As Integer
Dim Filename As String, FilePathName As String

Set RN = Selection

For j = 2 To RN.End(xlDown).Row
    Filename = RN(j)
    FilePathName = RN(1) & RN(j)
    With Worksheets(RN.Parent.CodeName)
        .Hyperlinks.Add Anchor:=.Range(RN(j).AddressLocal), _
            Address:=FilePathName, _
            TextToDisplay:=Filename
    End With
Next
End Sub

select range with first cell have the file path and then the filenames there after
e.g
c:\pdf folder\
book1.pdf
book2.pdf
etc....