asked on

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

Rodney Endriga

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.

Jim Horn

ASKER

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.

Rodney Endriga

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.

Jim Horn

ASKER

Didn't work. Made the entire cell contents a hyperlink, which means =HYPERLINK(... is still visible, as opposed to just the name.

ASKER CERTIFIED SOLUTION

Rodney Endriga

membership

This solution is only available to members.

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

Start Free Trial

Jim Horn

ASKER

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.

Rodney Endriga

Sure, Jim. No Problem. I'm glad we were able to find a solution! Always glad to assist.