Consulting

Results 1 to 18 of 18

Thread: Solved: Change text to hyperlink

  1. #1

    Solved: Change text to hyperlink

    Hi. I hope someone is kind enough to assist.

    I am trying to convert the text from one column to a hyperlink in the next column.

    eg. If in cell A1, I have the text link.doc, I would like a hyperlink to be created in B1 with the hyperlink link.doc.

    So as long as there is a file named link.doc in the same directory as my spreadsheet, clicking on the new hyperlink in B1 would open the file.

    I hope I have explained that clearly. I would be grateful for any assistance.

    Thanks

  2. #2
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    You cannot just change a text for a hyperlink to a file, like you said you have a file called Link.doc just creating a hyperlink next to it with that etxt will not take you to the file it needs this format:
    [VBA]
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
    "C:\Documents and Settings\User\My Documents\Link.doc", _
    TextToDisplay:=Range("A1").Value''text that will show as the hyperlink
    [/VBA]
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  3. #3
    VBAX Regular
    Joined
    Aug 2005
    Posts
    42
    Location
    Hi

    You can use the Hyperlink function imbedded in Excel for this.
    Start Excel
    Go to Insert on the toolbar
    Click on insert Hyperlink, a diglog box will show up
    On Link to : click existing file or web page
    Look in : Current Folders and search fand click on the file that you want to link
    Change the Text to display at the top of the diglog box to the name you want to be shown in Excel, in this case, link.doc

    Note : Excel display a warning msg whenever you click on the hyperlink.

    hope this help.

    FY
    Our Greatest Glory is not in never falling, but in rising every time we fall

    There is great satisfaction in building good tools for others to use

  4. #4
    Thanks for your help guys, but I feel that I need to explain this a little more. I apologise for that.

    I have attached a zip file which demonstrates what I would like to do. Just unzip all the files in the same directory.

    Basically, in column A there is text. I would like to create a macro that uses the text in column A to create what is in column B - ie. hyperlinks using the text from column A.

    If you edit a hyperlink in column B, you will see that no more than the simple text from column A is necessary.

    As long as the linked files are in the same directory, the hyperlinks will work.

    (Incidentally, the jpeg files in the zip are just single white pixels, so you will probably not see anything by clicking on the hyperlink.)


    I hope that is a better explanation.

    Thank you

  5. #5
    Any takers, anyone? I would be grateful for any help.

  6. #6
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Perhaps something like this:
    [VBA]
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
    "C:\Documents and Settings\USER\My Documents\" & Range("A1").value & ".xls", TextToDisplay _
    :=Range("A1").Value
    [/VBA]
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  7. #7
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    You could do it in the worksheet_selection change event like this:
    [VBA]
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row = 1 Then Exit Sub
    If Target.Column > 1 Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    ActiveSheet.Hyperlinks.Add Anchor:=Target.Offset(0, 1), Address:= _
    "C:\Documents and Settings\USER\My Documents\" & Target.Value, TextToDisplay:= _
    Target.Value
    End Sub
    [/VBA]but you have to revisit the cell to create the hyperlink!
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    [vba]

    Public Sub ProcessData()
    Const TEST_COLUMN As String = "A" '<=== change to suit
    Const BASE_DIR = "C:\test\jacksonworld\" '<=== change to suit
    Dim i As Long
    Dim mpLastRow As Long
    Dim mpFilename As String

    With ActiveSheet

    mpLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
    For i = 1 To mpLastRow 'iLastRow to 1 Step -1

    mpFilename = Dir(BASE_DIR & .Cells(i, TEST_COLUMN).Value)
    If mpFilename <> "" Then

    .Cells(i, TEST_COLUMN).Hyperlinks.Add _
    Anchor:=.Cells(i, TEST_COLUMN), _
    Address:=BASE_DIR & mpFilename, _
    TextToDisplay:=.Cells(i, TEST_COLUMN).Value
    End If
    Next i

    End With

    End Sub
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Was mine a bit messy Bob?, if the Op called your procedure from the worksheet_change would it create the link after pressing enter?, with my version you enter text but after pressing enter you have to revisit the cell in order to complete the process....a bit odd, it would have been easy enought to create a procedure (not as sound as yours) to work on click of a button and turn all in to hyperlinks but i got the idea the Op wanted it after they had typed the name in the cell.
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  10. #10
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    No Simon, I think I was composing mine as you posted yours, so we just overlapped.

    Mine is batch routine which goes through an existing list and applies hyperlinks to them. I thought about using events, but passed in the end.

    You have to revisit because you are using SelectionChange. Use Change and it would be fine. Also, best to check if the file exists before setting the hyperlink

    [vba]

    Private Sub Worksheet_Change(ByVal Target As Range)
    Const BASE_DIR As String = "C:\test\jacksonworld\"
    If Target.Row = 1 Then Exit Sub
    If Target.Column > 1 Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If Dir(BASE_DIR & Target.Value) <> "" Then
    ActiveSheet.Hyperlinks.Add _
    Anchor:=Target.Offset(0, 1), _
    Address:=BASE_DIR & Target.Value, _
    TextToDisplay:=Target.Value
    End If
    End Sub
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  11. #11
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Nicely put........i can cope with clear, lucid explanations like that, drinking Stella has it's implications, especially when you consider i have an average of 22.8 billion neurons in my brain and without the aid of Stella i naturally lose on average 85,000 per day (means my brain has 268235.30 days or 734.9 years until empty!) and alcohol can cause further reductions of 0.1% per drinking session thats another 268 a day, multiplied by every day of the week not including weekends thats 70009 a year - all this equates to 31095009 nuerons spent each year which believe it or not means i only have 733.24 years left of brain time so you can see why i'm gradually getting dimmer in my old age!
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  12. #12
    Thanks guys. That is fantastic! I really appreciate it.

    It's a pity the base directory could not default to the same directory as the spreadsheet, but if it can't be done, so be it.

    Thanks again.

  13. #13
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    It can

    [vba]

    If Dir(ACtiveWorkbook.Path & Application.PathSeparator & Target.Value) <> "" Then
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  14. #14
    Ooh. Where does that fit in?

    I must be copying it to the wrong spot.

  15. #15
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Instead of

    [vba]

    If Dir(BASE_DIR & Target.Value) <> "" Then
    [/vba]


    __________________________________________
    UK Cambridge XL Users Conference 29-30 Nov
    http://www.exceluserconference.com/UKEUC.html
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  16. #16
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,286
    Location
    Quote Originally Posted by Simon Lloyd
    Nicely put........i can cope with clear, lucid explanations like that, drinking Stella has it's implications, especially when you consider i have an average of 22.8 billion neurons in my brain and without the aid of Stella i naturally lose on average 85,000 per day (means my brain has 268235.30 days or 734.9 years until empty!) and alcohol can cause further reductions of 0.1% per drinking session thats another 268 a day, multiplied by every day of the week not including weekends thats 70009 a year - all this equates to 31095009 nuerons spent each year which believe it or not means i only have 733.24 years left of brain time so you can see why i'm gradually getting dimmer in my old age!
    Just out of curiousity ... http://www.madsci.org/posts/archives...3301.Cb.r.html

  17. #17
    Ooh yeah!

    Thanks so much.

  18. #18
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Charlize, just went to that link, phew! thanks, it seems i was miss informed.....in actual fact by their reckoning i must be a bloomin' genius, guess i can add weekends to my stella drinking now!

    Lol
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

Posting Permissions

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