Enter column of hyperlinks
 

I have a sheet like this:
https://dl.dropboxusercontent.com/u/...inkEntry-1.jpg
I've pasted in the entire column A of over a hundred filenames.

In col B I want to automate the entry of their hyperlinks.

They are all from a single Explorer folder whose path is in A1. The
text is just the filename (preferably without the extension, but
that's unimportant.)

So the result would look like this:
https://dl.dropboxusercontent.com/u/...inkEntry-2.jpg
I've struggled with automating this for an hour or so, but in vain,
and would much appreciate some help please.

--
Terry, East Grinstead, UK

Enter column of hyperlinks
 

Hi Terry,

Am Fri, 04 Sep 2015 07:54:33 +0100 schrieb Terry Pinnell:

I have a sheet like this:
https://dl.dropboxusercontent.com/u/...inkEntry-1.jpg
I've pasted in the entire column A of over a hundred filenames.

So the result would look like this:
https://dl.dropboxusercontent.com/u/...inkEntry-2.jpg
I've struggled with automating this for an hour or so, but in vain,
and would much appreciate some help please.

try it with a macro:

Sub Hyp()
Dim LRow As Long
Dim rngC As Range
Dim myPath As String

With ActiveSheet
myPath = .Range("A1")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("A3:A" & LRow)
.Hyperlinks.Add _
anchor:=rngC.Offset(, 1), _
Address:=myPath & rngC, _
TextToDisplay:=Left(rngC, InStr(rngC, ".") - 1)
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Enter column of hyperlinks
 

Hi Terry,

Am Fri, 4 Sep 2015 10:17:55 +0200 schrieb Claus Busch:

try it with a macro:

Sub Hyp()

there is missing a backslash

Sub Hyp()
Dim LRow As Long
Dim rngC As Range
Dim myPath As String

With ActiveSheet
myPath = .Range("A1")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("A3:A" & LRow)
.Hyperlinks.Add _
anchor:=rngC.Offset(, 1), _
Address:=myPath & "\" & rngC, _
TextToDisplay:=Left(rngC, InStr(rngC, ".") - 1)
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Enter column of hyperlinks
 

Claus Busch wrote:

Hi Terry,

Am Fri, 4 Sep 2015 10:17:55 +0200 schrieb Claus Busch:

try it with a macro:

Sub Hyp()

there is missing a backslash

Sub Hyp()
Dim LRow As Long
Dim rngC As Range
Dim myPath As String

With ActiveSheet
myPath = .Range("A1")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("A3:A" & LRow)
.Hyperlinks.Add _
anchor:=rngC.Offset(, 1), _
Address:=myPath & "\" & rngC, _
TextToDisplay:=Left(rngC, InStr(rngC, ".") - 1)
Next
End With
End Sub


Regards
Claus B.

Hi Claus,

Thanks so much, that works perfectly!

You are truly one of my major online assets ;-)

--
Terry, East Grinstead, UK

Enter column of hyperlinks
 

Hi Terry,

Am Fri, 04 Sep 2015 10:48:12 +0100 schrieb Terry Pinnell:

Thanks so much, that works perfectly!

You are truly one of my major online assets ;-)

you are welcome. Always glad to help.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional