PDA

View Full Version : Solved: Change text to hyperlink



jacksonworld
11-13-2007, 08:32 PM
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

Simon Lloyd
11-14-2007, 12:58 AM
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:

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

francis
11-14-2007, 01:30 AM
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

jacksonworld
11-14-2007, 03:10 AM
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

jacksonworld
11-17-2007, 01:29 AM
Any takers, anyone? I would be grateful for any help. :help

Simon Lloyd
11-17-2007, 03:50 AM
Perhaps something like this:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"C:\Documents and Settings\USER\My Documents\" & Range("A1").value & ".xls", TextToDisplay _
:=Range("A1").Value

Simon Lloyd
11-17-2007, 03:55 AM
You could do it in the worksheet_selection change event like this:

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
but you have to revisit the cell to create the hyperlink!

xld
11-17-2007, 04:10 AM
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

Simon Lloyd
11-17-2007, 04:51 AM
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.

xld
11-17-2007, 05:16 AM
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



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

Simon Lloyd
11-17-2007, 05:52 AM
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!

jacksonworld
11-21-2007, 06:50 PM
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.:thumb

xld
11-22-2007, 01:05 AM
It can



If Dir(ACtiveWorkbook.Path & Application.PathSeparator & Target.Value) <> "" Then

jacksonworld
11-22-2007, 01:53 AM
Ooh. Where does that fit in? :bouncy:

I must be copying it to the wrong spot.

xld
11-22-2007, 02:49 AM
Instead of



If Dir(BASE_DIR & Target.Value) <> "" Then



__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

Charlize
11-22-2007, 08:52 AM
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/2002-03/1016223301.Cb.r.html

jacksonworld
11-22-2007, 04:17 PM
Ooh yeah!

Thanks so much.

Simon Lloyd
11-23-2007, 03:45 AM
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