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
LVL 27
yo_beeDirector of Information TechnologyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FamousMortimerCommented:
Hi Mike,

I don't think you can import the data as a hyperlink.

Not sure if this is the best way to do it, but it will work.  you can call this function after the data is dropped onto the sheet.  Assuming the hyperlink is in column B.

Sub AddHyperlinks()
    Dim c As Range
    For Each c In Range("B2:B" & Range("B1048576").End(xlUp).Row)
        c.Hyperlinks.Add c, c.Text
    Next c
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yo_beeDirector of Information TechnologyAuthor Commented:
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?
0
FamousMortimerCommented:
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

0
yo_beeDirector of Information TechnologyAuthor Commented:
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
0
yo_beeDirector of Information TechnologyAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.