Automatic hyperlinks in Excel?

Question

I am exposing data from a SSAS server to which users can connect from any tool they like, (in practise usually Excel 2007). We would like to provide URLs in some cells and would like Excel to recognise these as hyperlinks and make active and format accordingly.

When we put a URL into a cell, it is just rendered as plain text. If you touch the cell and click out again then Excel activates it as a link.

The same behaviour occurs if you source the data direct from SQL, or from a CSV file, there's nothing special about our use of SSAS here.

Does anyone know a way to make these render as links automatically? We have no control over the generation of the spreadsheet so macros are out.

EDIT:

Just received the response from Meff. Good point, however I forgot to mention that the place we are showing the URLs is in the target of a drillthough action.

Answer 1

I don't believe this will be possible without a macro.

A hyperlink in an Excel cell has nothing to do with the value of the cell. It is a seperate object that is associated with the cell. The fully functional way to add a hyperlink to a cell is via the Excel menu item Insert -> Hyperlink.

The fact that a hyperlink is added when a user enters a value such as 'http://...', 'https://...' or 'ftp://...' is simply a GUI shortcut which has the same effect as Insert -> Hyperlink. In your example, when the user 'touches' the cell, they effectively enter the 'http://...' value into the cell and GUI shortcut for Insert -> Hyperlink is invoked. The shortcut is, however, NOT invoked when values are entered into cells programtically, either via VBA or built in Excel features (e.g. Data -> Import External Data).

For this reason, it is indeed a challenge to make these arbitrary data imports render appropriate values as hyperlinks without some form of macro. If you can persuade your users to install a simple Excel add-in, you could provide a menu item which runs the following simple code:-

Dim cell as Range : For Each cell in Selection.Cells // could also use Range("A1").CurrentRegion.Cells or similar
    If Left(cell.Value, 7) = "http://" Or Left(cell.Value, 8) = "https://" Or Left(cell.Value, 6) = "ftp://" Then
        Call cell.HyperLinks.Add(cell, cell.Value)
    End If
Next cell

The user could invoke this after importing/refreshing data. Obviously it's far from ideal because it relies on the user taking an extra step in order to have the hyperlinks rendered.

Answer 2

Take a look at URL actions in SSAS, this allows them to right-click the cell and be offered the ability to go to a link in the right-click-menu:

http://timlaqua.com/2009/03/ssas-cube-action-cells-target-type-url-action-type-example/