Printable View
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
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]
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
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
Any takers, anyone? I would be grateful for any help. :help
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]
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!
[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]
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.
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]
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!
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
It can
[vba]
If Dir(ACtiveWorkbook.Path & Application.PathSeparator & Target.Value) <> "" Then
[/vba]
Ooh. Where does that fit in? :bouncy:
I must be copying it to the wrong spot.
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
Just out of curiousity ... http://www.madsci.org/posts/archives...3301.Cb.r.htmlQuote:
Originally Posted by Simon Lloyd
Ooh yeah!
Thanks so much.
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