Data connection with =HYPERLINK() only renders as a hyperlink after editing the cell

Continuation of this question..

Hi All

I have an Excel 2010, 32-bit spreadsheet with a connection to SQL Server where one of the columns / 500ish rows is a =HYPERLINK().   My problem is, when I do a Data tab: Refresh, it posts the function in the cells NOT as a hyperlink, and it only renders like a hyperlink when I click in the cell and hit the <return> key.

Before editing the cell appears like this (mocked up)
=HYPERLINK("https://sg42.salesforce.com/001AA000018NXZx9ZIAS", "MATTHEWS, PATRICK")

Open in new window

After editing only the name appears, blue and underlined.

Question:  How to get the above function to render as a hyperlink when the Data>Refresh All is clicked?

Thanks.
Jim
LVL 66
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
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.

Rodney EndrigaData AnalystCommented:
Hi Jim, you can use a macro to activate the hyperlinks in your range:

Sub EE_HyperlinkAdd()

    'Converts each text hyperlink selected into a working hyperlink

    For Each xCell In Selection
        ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
    Next xCell

End Sub

Another (long & tedious) method is to press the 'F2' key & then Enter Key on your keyboard for each of the cells you would like to activate. It will activate the hyperlink formula and leave the 'Friendly Name' as the displayed link.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Can you edit the above Excel VBA code to perform this action on two tabs (say 'one' and 'two'), Column A in each?  

I can include a 'Refresh All' button that performs all the connection refreshes, and then the above code.

The 'F2' approach is not practical as this will be kicked out to users, who will need to refresh on the fly.
0
Rodney EndrigaData AnalystCommented:
I have adjusted the code above to apply it to 2 sheets (using Column A as the key hyperlink value):

Sub EE_HyperlinkAdd()
Application.ScreenUpdating=False
'Converts each text hyperlink selected into a working hyperlink; 2 worksheets checked
Dim ws1 As Worksheet, ws2 As Worksheet, rng1 As Range, rng2 As Range

Set ws1 = Sheets("Sheet1")
ws1.Activate
Set rng1 = ws1.Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
For Each xCell In rng1
    ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell

Set ws2 = Sheets("Sheet2")
ws2.Activate
Set rng2 = ws2.Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
For Each xCell In rng2
    ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
Application.ScreenUpdating=True
 End Sub

Let me know if this works for you. Thanks.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Didn't work.  Made the entire cell contents a hyperlink, which means =HYPERLINK(... is still visible, as opposed to just the name.
0
Rodney EndrigaData AnalystCommented:
Try this code please:

Sub EE_HyperlinkAdd()
 Application.ScreenUpdating=False
 'Converts each text hyperlink selected into a working hyperlink; 2 worksheets checked
 Dim ws1 As Worksheet, ws2 As Worksheet, rng1 As Range, rng2 As Range

 Set ws1 = Sheets("Sheet1")
 ws1.Activate
 Set rng1 = ws1.Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
 For Each xCell In rng1
      xCell.NumberFormat = "General"      
      xCell.FormulaR1C1=xCell.Formula
 Next xCell

 Set ws2 = Sheets("Sheet2")
 ws2.Activate
 Set rng2 = ws2.Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
 For Each xCell In rng2
     xCell.NumberFormat = "General"      
     xCell.FormulaR1C1=xCell.Formula
 Next xCell
 Application.ScreenUpdating=True
  End Sub

 Let me know if this works for you. Thanks.
This assumes you will be bringing in data to an Excel Sheet in this format:
=HYPERLINK("https://sg42.salesforce.com/001AA000018NXZx9ZIAS", "MATTHEWS, PATRICK")
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
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Bingo bango, we have a winner.  Thanks.

btw I have an article out there called Microsoft Excel & SQL Server:  Self service BI to give users the data they want.  Let me know if you're okay with me making a quick addition to mention this question, and giving you credit of course.
0
Rodney EndrigaData AnalystCommented:
Sure, Jim. No Problem. I'm glad we were able to find a solution! Always glad to assist.
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 Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.