Automating Hyperlinks

Clo

New Member
Joined
Oct 26, 2006
Messages
1
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,919
Office Version
  1. 365
Platform
  1. Windows
Why not use the HYPERLINK worksheet function?
 

bbrnx19

Board Regular
Joined
Oct 25, 2006
Messages
148
Clo
Try this out
Code:
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....
 

Watch MrExcel Video

Forum statistics

Threads
1,114,084
Messages
5,545,870
Members
410,711
Latest member
Josh324
Top