Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Hyperlink with different text

    I have a cell that creates a hyperlink by concatenating the contents of other cells, like this.

    =HYPERLINK("http://www.example.com?"&"name="&A1&"&address="&B1)

    When I save the worksheet as a PDF, the link appears and works fine.


    However, if I include the "friendly text" parameter in the hyperlink, like this ...

    =HYPERLINK("http://www.example.com?"&"name="&A1&"&address="&B1, "Click here")

    ... then when I save the worksheet as a PDF, the link correctly appears as "Click here", but it doesn't work.


    Is there a way to make this work, or is there another way to achieve the same effect?
    Last edited by Murgatroyd; 2016-07-20 at 22:00.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Murgatroyd

    Copy the cell with
    =HYPERLINK("http://www.example.com?"&"name="&A1&"&address="&B1, "Click here")
    formula into Word.
    Now copy back from Word into the cell.
    The hyperlink is no longer an equation, but it retains the hyperlink and displays your 'friendly text' Click here
    Now save as pdf.

    the pdf link should work

    zeddy

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. I tried your suggestion, but the result was similar to before. The text was plain rather than blue underlined, but the link still wasn't retained in the PDF.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Murgatroyd

    I tested this OK before I posted.
    So let's try this again.

    In the attached files,
    1. starting with file [rz-test-0.xlsx]
    ..I put a hyperlink formula in cell [C12]

    2. I copy cell to a blank empty Word document
    ..see attached [test-0.docx]

    3. I use Ctrl-C to copy the text from the Word doc

    4. returning to the Excel file, I paste the copied text back into cell [C12]
    ..I then save the Excel file as [rz-test-1.xlsx] see attached

    5. Then I save the Excel file as a pdf file [rz-test-1.pdf]
    ..see attached

    6. then I double-click the [rz-test-1.pdf] file in Explorer, which opens the pdf file.
    7. I click the text Click here in the pdf file and the link works

    zeddy
    Attached Files Attached Files

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks, I get the same result.

    However, in rz-test-0.xlsx, if [A1] is changed, then the hyperlink in [C12] reflects the change, but in rz-test-1.xlsx, if [A1] is changed, then the hyperlink in [C12] does not reflect the change.

    I am using the worksheet as an invoice template, and the hyperlink needs to reflect changes in the cells that it references, without having to copy to/from Word after each change.

    Is there a way to achieve the reformatting of the hyperlink within Excel itself, either automatically or via a macro? (What is the copying to/from Word doing to the data?)
    Last edited by Murgatroyd; 2016-07-21 at 22:10.

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Murgatroyd

    Is there a way to achieve the reformatting of the hyperlink within Excel itself, either automatically or via a macro?
    Yes, that can be done.
    I'll post a solution when I get back after my checkup.

    zeddy

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    OK thanks.

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Murgatroyd

    OK. So my post#2 and post#4 answered what you asked for in your post#1
    Your post#5 is asking a different question i.e. "after saving as a pdf with the pdf link working, keep the formula in the Excel sheet"

    So the attached file does this, using a vba routine assigned to a button on the worksheet [Save sheet as pdf]
    This assumes your hyperlink formula is in cell [C10]
    You can easily adapt the code to match your requirement.
    Also, the pdf file will be named the same as the current excel file, in the same current folder, but with a .pdf file extension.
    If the pdf file already exists, it will be overwritten.
    The code can be adapted if you want to specify different folders and filenames etc etc etc

    zeddy
    PS checkup went OK
    Attached Files Attached Files

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks. I clicked on the button, but it did not create a PDF.

    However, as I don't want to automate the file saving, I adapted part of your code into a macro to insert a hyperlink that works when saved as a PDF.

    Code:
    zURL = "http://www.example.com?&name=" & [A1] & "&address=" & [B1]
    
    ActiveSheet.Hyperlinks.Add Anchor:=[C10], _
    Address:=zURL, TextToDisplay:="Click here"
    In my worksheet, the hyperlink cell is actually a group of merged cells (to fit all the text, which is actually much longer than just "Click here"). With the above code, only the first few characters of the text is clickable in the PDF (i.e., where cell C10 is), but is it possible to make the hyperlink anchor cover a range of cells (e.g., C10:E12), so that the whole range is clickable in the PDF?

    Also, each time I run the macro, the hyperlink text reverts to the default style (Calibri 11 pt). Is it possible to specify the style for the hyperlink text cells in the code (Arial 9 pt)?
    Last edited by Murgatroyd; 2016-07-23 at 02:17.

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Murgatroyd

    My posted file in post#8 creates the pdf on my system.
    (it's in the same location as the excel file; you could try to search for it).
    (You need to save the posted file first of course, and then load it from your saved location - if you 'open' the posted file directly, rather than save, you are working in an Excel twilight zone - much like opening an excel file directly from an email attachment rather than saving the attachment and then loading the attachment etc etc etc)

    You can certainly use a merged group of cells for your hyperlink.
    see attached version.
    You only need to refer to the first cell in the merged group i.e. Anchor=[C10] or Anchor = [C10:E12] can be used.

    You can change any of the Excel styles on your system.
    Or you can use vba code to modify to suit your requirement.
    Code:
    [c10] = zFormula
    [c10].Style = "Hyperlink"
    [c10].Font.Name = "Arial"
    [c10].Font.Size = 9
    The range that is clickable in the pdf seems to be dependant on the width of the left-most column of the merged cells group.
    (i.e. if you make column [C] wider, this affects what is the 'clickable' edge-limit in the pdf exported doc.)

    Perhaps some of our other helpers can tell us why this is.

    zeddy
    Attached Files Attached Files

  11. The Following User Says Thank You to zeddy For This Useful Post:

    Murgatroyd (2016-07-23)

  12. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks again.

    I found that your file did create a PDF but in my default document folder.

    To work around the limitation of the clickable area in the PDF where the hyperlink covers a block of merged cells, I rearranged the layout so that the hyperlink covers only a single cell with only a short text, which does the job.

  13. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Murgatroyd

    Thanks for the follow up.
    Your solution to use a single cell would do the job nicely.
    Note: You can always increase the row width (as well as column width) and use word-wrap alignment within the cell if you need longer text.

    I forgot to reply to your question
    (What is the copying to/from Word doing to the data?)
    ..the copy to/from Word simply 'converts' the =HYPERLINK formula to a 'value', while retaining the link.

    zeddy

  14. #13
    Star Lounger
    Join Date
    Feb 2004
    Posts
    92
    Thanks
    2
    Thanked 6 Times in 6 Posts
    Zeddy, AWESOME Excel knowledge! I have been considered the Excel "expert" in my office since Office 2003, but this post made me feel inadequate! Like a poseur even. Hats off to you my friend. Great job!

  15. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi joela44

    Thank you for your kind words!
    It's always nice to be appreciated, whatever you do.
    There are many others in this Lounge that I really look up to too.
    I've learned much from them, and continue to do so (RG, Maud, Rory, and many others, et al you know who you are!).
    Hats off to you for being a 'goto guy' in your office! We've been there!
    Helping others is good for your health!
    I hope you continue to enjoy using Excel as much as I do!

    zeddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •