HyperLink with Dynamic file-address using cell value

Question

i've been googling this for a while but can't find a proper solution.

I want to have Hyperlink which retrives it's (parts of its) address from a certain cell. I.e. when inserting a hyperlink i want it to refer to a cell value rather than an absolut string so the hyperlinks can easily be changed.

Followed is the sub made:

Sub CreateHyper(ARow As Integer, AColumn As Integer, ASheet As Integer, TargetAdress As String,      HName As String)
Sheets(1).Hyperlinks.Add Anchor:=Sheets(ASheet).Cells(ARow, AColumn), _
Address:="'" & Sheets("DATA").Range("A2").Value & "'" & TargetAdress, TextToDisplay:=HName
End Sub

The cell value in A2 is dependent on where the excel is stored could be C:\ or X:\alot_of_folders \ ...\

TargetAdress is not to a cell but to a file on the drive. ...\File.pdf

I Think all other variables are self-explanitory.

So when "A2" is updated all hyperlinks should be updated aswell.

The following works, but the target will not change if A2 is changed since it will be a static, how to make "Sheets("DATA").Range("A2").value a reference?

Sub CreateHyper(ARow As Integer, AColumn As Integer, ASheet As Integer, TargetAdress As String,      HName As String)
Sheets(1).Hyperlinks.Add Anchor:=Sheets(ASheet).Cells(ARow, AColumn), _
Address:=Sheets("DATA").Range("A2").Value & TargetAdress, TextToDisplay:=HName
End Sub

Any suggestions?

Normally the TargetAdress includes the whole path (so that works) but now i want a cell reference in the address, it's the reference bit i can't manage. A2 includes = c:\folder1\ Hyperlink will be: c:\folder1\folder2\file.pdf If i update A2 to: c:\folder3\ How will the hyperlink now be updated to c:\folder3\folder2\file.pdf If you remove the "'" bits then it works, but it will static and will not change targetaddress if A2 changes. — Damien

teylyn teylyn · Accepted Answer · 2014-09-28T20:55:24

AFAIK, creating a hyperlink with Hyperlinks.Add will never be dynamic, since the Address will be whatever was the content of that cell at the time the link was created. If that cell changes, the link will not update. You would need to repeat running that sub in order to use the new cell content as a link.

Instead of placing a hyperlink into a cell this way, consider using the Hyperlink worksheet function, which and source the link address dynamically from a cell.

Sub CreateHyper(ARow As Integer, AColumn As Integer, ASheet As Integer, TargetAddress As String, HName As String)
Dim TargetCell As Range
Set TargetCell = ThisWorkbook.Sheets(ASheet).Cells(ARow, AColumn)
TargetCell.Formula = "=hyperlink('Data'!A2&""" & TargetAddress & """,""" & HName & """)"
End Sub

Note that I used the proper spelling of Address in the variable names. Your original code spells it incorrectly, which may lead to confusion when debugging.

HyperLink with Dynamic file-address using cell value

1 Answers