Convert Inserted HTML text as Hyperlink

[carmaboy]

Is it possible when importing from another data source say SQL Server
stored in table as a href and have excel evaluate the text as
hyperlink.

i.e Import data <a href="http://www.microsoft.com">Microsoft</a> into
cell A1.

Excel 2007 sees the data as text until you click into the cell then
off of it then it converts the cell to read only Microsoft as a
hyperlink.

I want to avoid using macro's but hoping there is an addon or a
setting to force XLS to automatically refresh/reevaluate.

TIA

 

[JLatham]

I know you asked for a method other than a macro, but I don't know of a
setting that's going to do this for you. So, here's one possible solution.
It's a macro that runs automatically without the user having to choose it and
run it. It is attached to the worksheet's _Change() event. To put it into
the proper place,
Right-click on the sheet's name tab and choose View Code from the list that
appears.
Copy the code below and paste it into the code module presented to you and
close the VB Editor window.
Now when you enter anything beginning with <a href= it will convert it to a
=HYPERLINK() formula using the pieces of the HTML statement as the 2
parameters for the formula.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim hLink As String
Dim hLocation As String
Dim tagPosition As Integer

If Target.Cells.Count > 1 Then
Exit Sub ' only work when just 1 cell changed
End If
If UCase(Left(Target, 8)) = "<A HREF=" Then
hLink = Mid(Target, 9, InStrRev(Target, """") - 8)
MsgBox "hLink = '" & hLink & "'"
tagPosition = InStr(Target, ">")
hLocation = Chr$(34) & Mid(Target, tagPosition + 1, _
Len(Target) - 4 - tagPosition) & Chr$(34)
Target.Offset(0, 1).Formula = "=HYPERLINK(" & _
hLink & "," & hLocation & ")"
End If
End Sub

 

[JLatham]

This version will work better for you. I had the formula going into the cell
in the next column for testing, this corrects it and overwrites the <a href=
entry with the HYPERLINK() formula.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim hLink As String
Dim hLocation As String
Dim tagPosition As Integer

If Target.Cells.Count > 1 Then
Exit Sub ' only work when just 1 cell changed
End If
If UCase(Left(Target, 8)) = "<A HREF=" Then
hLink = Mid(Target, 9, InStrRev(Target, """") - 8)
MsgBox "hLink = '" & hLink & "'"
tagPosition = InStr(Target, ">")
hLocation = Chr$(34) & Mid(Target, tagPosition + 1, _
Len(Target) - 4 - tagPosition) & Chr$(34)
Target.Formula = "=HYPERLINK(" & _
hLink & "," & hLocation & ")"
End If
End Sub

 

[Desk Jockey]

Good stuff. But it only seems to work for a single cell. What can be done
if we want to run a SQL query against a database and wrap one of the columns
of the query with an HREF tag?

Embedding the HYPERLINK macro into the SELECT results seems to do the trick:

select '=HYPERLINK("http://www.microsoft.com", "Microsoft")'
FROM <tables, joins, where, etc.>

seems to do the trick. Of course you'll be interested in a result from the
query and replace the "Microsoft" with your actual result from the query.