automatically creating hyperlinks

[Corentin Cras-Méneur]

Hi All,


I had a question that puzzled me for a while and I was wondering
wether anyone in the newsgroup had any idea about this: I've had to
generate and transfrom a lot of Excel pages lately. I had long lists of
numbers that I needed to transform into active hyperlinks (constructed
from these numbers).
The *dirty* way that I used was to get the list in BBEdit and use
regular expressions to transform the list into properly formatted html
links

find:
^(\d+)

replace with:
<a
href="http://www.ncbi.nlm.nih.gov/LocusLink/LocRpt.cgi?l=\1">\1</a><br>

and paste the list between html headers. The resulting html file can be
opened in Excel allowing me to copy and paste the list of links to any
spreadsheet.

Is there a more elegant way to do this directly in Excel ??? I can
easely transfor the list of numbers into the proper http link but the
cell is not an active link. Pressing command-k on the cells clearly
shows the link is used for "display" but not for the link itself.

Is there an easy way to transform all these http links into active links
in the srpeadsheet???

Thanks,


Corentin

 

[JE McGimpsey]

Is there an easy way to transform all these http links into active links
in the srpeadsheet???

Would this work for you?

Public Sub ConvertNumbersToHyperlinks()
Const sURI As String = _
"http://www.ncbi.nlm.nih.gov/LocusLink/LocRpt.cgi?l="
Dim rCell As Range
Dim rConvert As Range

Application.ScreenUpdating = False
On Error Resume Next 'in case no numbers
With ActiveSheet
.Hyperlinks.Delete
Set rConvert = .Cells.SpecialCells( _
xlCellTypeConstants, xlNumbers)
On Error GoTo 0
If Not rConvert Is Nothing Then
For Each rCell In rConvert
With rCell
ActiveSheet.Hyperlinks.Add _
Anchor:=.Cells, _
Address:=sURI & .Text
End With
Next rCell
End If
End With
Application.ScreenUpdating = True
End Sub

 

[Corentin Cras-Méneur]

Would this work for you?

John, it works like a charm !!! And it's fast )) I REALLY have to
get into VBA. I obviously can find workaround through other approaches,
but it's beginning to be ridiculous :-\

I tried to do it through Macros, but I couldn't find out how to get
the number to be used for "Anchor" (the anchor term wasn't so clear for
me to start with).

Thanks again John ))


Corentin

 

[JE McGimpsey]

John, it works like a charm !!! And it's fast ))

Glad to be of service!

 

[Corentin Cras-Méneur]

Glad to be of service!

I knew you'd have a solution John. I tried for a while to play around
with it before I gave up and posted to the group.
Thanks again,


Corentin