Making a dynamic Hyperlink function static

2
votes

I had a column of values in column A, all with 6 digit numbers. In order to hyperlink each of these to a folder containing PDF files with the same 6-digit titles, I inserted the following function into cell A2:

=HYPERLINK("C:\...Project\" & A2 & ".pdf", A2)

This worked perfectly, and dragging it down through the thousand or so cells created hyperlinks for each entry.

However, now I want to replace column A1 with these new hyperlinks. I thought I could paste-special the column of hyperlinks to remove the dynamic entries in each cells, but this doesn't seem to be possible. I'm sure there's an easier solution. Am I missing something? Can I make column B exist independent of column A before replacing it? Is there a VBA solution?

2
excelvba
Sure, that would be great too – user1996971
Looks like I've already been beaten to it below... – Tim Edwards
Thanks for your input! – user1996971

2 Answers

3
votes

You can run this quickly in vba procedure:

Sub CreateHyperlinks()
Dim cl As Range

For Each cl In Range("B1:B100").Cells '## Modify as needed
    cl.Hyperlinks.Add cl, cl.Value, , , cl.Offset(0,-1).Value
Next

End Sub
1
votes

If by any chance anyone wants to do it without VBA, you can replace for example

=HYPERLINK(1,2)

with

<html><a href=1>2

but in cases with dynamic formulas it's too complicated because 

=HYPERLINK("C:\...Project\" & A2 & ".pdf", A2)

will be

="<html><a href=""C:\...Project\" & A2 & ".pdf"">" & A2

and then it has to be copied as text (for example paste the values in Notepad and copy them again in Notepad) before pasting in Excel.