asked on

How to convert a String to Hyperlink when imported from ODBC import

I have a ODBC connection to a MSSQL DB. One of the columns contains a url \\Fileserver\File.
When I import this into excel it is just a string at first, but after I click in the cell and out the cell is converted to a hyperlink.

I know I can use the =HYPERLINK() function, but I want to see if I can avoid that step and just have the cell convert when imported.

Is this possible?

Thanks

Mike -Yo_Bee- B

ASKER CERTIFIED SOLUTION

FamousMortimer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

yo_bee

ASKER

Ok.
I was hoping not to have to do that, but if that is the case so be it.

Can I have this function run automatically after the query up is complete?

FamousMortimer

Sure, I changed the function so you can call it like below after the data is imported

Call AddHyperlink("B") ' this will hyperlink column B

Sub AddHyperlinks(ByVal ColumnLetter As String)
    Dim c As Range
    For Each c In Range(ColumnLetter & "2:" & ColumnLetter & Range(ColumnLetter & "1048576").End(xlUp).Row)
        If LCase(Left(c.Value, 4)) = "http" Then c.Hyperlinks.Add c, c.Text
    Next c
End Sub

Open in new window

yo_bee

ASKER

I have validated your sub via a manual run.
And not sure how to call this sub from a with event after query update.
Do you know how to catch the after query update to call the sub. Your previous reply was not clear to me. I am not vba guru. Any pointers would be great. If not you i will close the question and award the points.

Thanks

Mike

yo_bee

ASKER

Thanks for your help.
I modified your suggestion with some code I used in another macro.

Sub AddHyperlinks()

' Dim c As Range
Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
'MsgBox (LastRow & vbNewLine & LastCol)
End With

For Each c In Range("P2:P" & LastRow)
c.Hyperlinks.Add c, c.Text
Next c
End Sub