Hyperlink Field From SQL Server to Excel

I have a SQL Server table that is displayed in an Excel spreadsheet.

One of the fields is a path to a location on our network (all users have access to the network).

The path transfers over fine, but it won't open via a click on the cell in Excel.  I have tried adding the "file://" prefix, but that doesn't help.
SELECT
    Yadda1,
    Yadda2,
    Yadda3,
    'file://' + Path AS PathName
FROM
    MyTable

Open in new window


And tried using the Hyperlink Excel keyword as such:
SELECT
    Yadda1,
    Yadda2,
    Yadda3,
    '=HYPERLINK("file://' + Path + '")' AS PathName
FROM
    MyTable

Open in new window


Any ideas on how I can get it to work?

TIA
LVL 10
ClifAsked:
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.

David ToddSenior DBACommented:
Hi,

Isn't this an Excel formatting issue rather than a SQL query issue?

Regards
  David
0
ClifAuthor Commented:
I don't know, which is why I posted it to both topics.
0
David ToddSenior DBACommented:
Hi Clif,

I think that it might be an excel formatting issue.

That is, if you right click on a cell, the last item is hyperlink. Record a macro if that helps find the actual commands for vba

HTH
  David
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ClifAuthor Commented:
I'm hoping this could be automated (ie, no user intervention)
0
David ToddSenior DBACommented:
Hi Clif,

Record the macro as mentioned above, look at how Excel codes it, then add it to your code ...

How is the data getting from sql to excel? Is it pushed? (ie SSIS is exporting data to excel) Is it pulled? (ie Excel macro selecting data from db)

Regards
  David
0
ClifAuthor Commented:
It's pulled.  The user has the spreadsheet on his desktop and refreshes the data from the button on the Data tab.
0
David ToddSenior DBACommented:
Suggest then, recording the button click as a macro, and then adding in the formatting that I suggested above.

Easy!

Regards
  David
0
yo_beeDirector of Information TechnologyCommented:
This seems very similar to my situation.
Here is my question and the solution I used (Slightly modified) to address this, but  it still requires human intervention.
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_28245228.html
I have the Macro to address the Sting to Hyperlink.

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 

Open in new window


I am working on VBA Class Module for WithEvents for QueryTable so that the above script runs after a AfterUpdate event.

If I can get this second part to work I think I have my solution.

I am having trouble with the WithEvents so I have a new question post for this.
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_28246274.html
0
yo_beeDirector of Information TechnologyCommented:
I have my vba that will update the column that I want to be converted to hyperlink.
Here is the second part where the WithEvents AfterRefresh is being watched.

Here is the question I posted and the solution that resolved the Afterrefresh event
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_28246274.html
Class Module
Option Explicit

Public WithEvents qtQueryTable  As QueryTable
Sub InitQueryEvent(QT As Object)
 
  Set qtQueryTable = QT

End Sub
Private Sub qtQueryTable_AfterRefresh(ByVal Success As Boolean)


   Dim c As Range
    Dim LastRow As Long
    Dim LastCol As Long
    
    't
      With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    ''For testing you can unremark the line below for some sort of confirmation
        'MsgBox (LastRow & vbNewLine & LastCol)
        End With
    

    For Each c In Range("P2:P" & LastRow)
        c.Hyperlinks.Add c, c.Text
    Next c
     ''For testing you can unremark the line below for some sort of confirmation
'  If Success Then
'' Query completed successfully
'     MsgBox "Success!"
'  Else
'' Query failed or was cancelled
'     MsgBox "Failed"
'  End If

End Sub

Open in new window


ThisWorkBook Module
Option Explicit

Dim clsQueryTable                                       As New clsModQT
Sub RunInitQTEvent()
  'Note that the Images Name needs to be replaced with the sheet name you have named it
  clsQueryTable.InitQueryEvent QT:=Worksheets("Images").ListObjects(1).QueryTable
End Sub
Private Sub Workbook_Open()

  Call RunInitQTEvent
  
End Sub

Open in new window

0
yo_beeDirector of Information TechnologyCommented:
I made a slight mod to the THISWORKBOOK Module
I noticed that it did not run correctly upon opening if you had the query Update when Open option selected.

I unchecked that option and added  Activeworkbook.Refreshall method after I called the RunInitQTevent.  This then executed as planned.  The column is updates with hyperlinks rather than just a test string.

Option Explicit

Dim clsQueryTable                                       As New clsModQT
Sub RunInitQTEvent()
  'Note that the Images Name needs to be replaced with the sheet name you have named it
  clsQueryTable.InitQueryEvent QT:=Worksheets("Images").ListObjects(1).QueryTable
End Sub
Private Sub Workbook_Open()

  Call RunInitQTEvent
  ActiveWorkbook.RefreshAll 
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
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 SQL Server

From novice to tech pro — start learning today.