Excel PDF conversion - hyperlink formula is not clickable

2
votes

How can I get the Excel hyperlink formula to result in clickable links when spreadsheets are saved as PDFs?

I have a simple spreadsheet with links to websites which I need to convert to PDF with clickable hyperlinks. If I manually add links through the Insert Hyperlink menu the links are clickable in the resulting PDF. However, when I use the hyperlink formula as below with the same link, it works in Excel but NOT when I save the file to PDF (either by printing to PDF or converting it with the Acrobat toolbar). 

=hyperlink(A1,B1)

Where A1 has the link location (for example http://www.zillow.com) and B1 has the friendly name.

I realize I could add links with the Insert Hyperlink menu, but I have a column of them and the hyperlink formula would be a better option. If it matters, I am using Excel 2010 and Adobe Acrobat XI Pro.

1
excelpdfhyperlinkexcel-formulaadobe
one option is to use VBA to loop through the column and add the hyperlink (like inserting hyperlink through the menu) since PDF only recognizes the formula as text. Unfortunately, there's no way to use the hyperlink formula because PDF will just render the friendly name (the formula result) as text. - Scott Holtzman
Thanks Scott, that should work if there's no fix for the hyperlink formula. - ELW

1 Answers

4
votes

I'm not sure how comfortable you are with VBA, but here is an example of what Scott was getting at.

This starts in a cell that you selected, and places a hyperlink in that cell with the hyperlink equal to whats in column A, and the text equal to what's in column B. It continues to do this until the data in column A ends (until it hits a blank space).

Sub HLink()

    Temp = Cells(Selection.Row, 1).Text
    While Not Len(Temp) = 0
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Temp, TextToDisplay:=Cells(Selection.Row, 2).Text
        Selection.Offset(1).Select: Temp = Cells(Selection.Row, 1).Text
    Wend

End Sub