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 p开发者_StackOverflowlace we are showing the URLs is in the target of a drillthough action.

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.

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/

without VBA....I found the easiest way to do this is just to record a macro & apply the shortcut key ( like ctrl -L) to the macro. The end users know that if they want to active the "links" they click CTRL-L. My macro was recorded as..... highlight the column, click data,text to columns, next,next,next,finish) then click off the top left cell a1 just for good measure. I had to save the file as an xlsm file (not sure why) in 2013

An easy way to manually update many links is to use set the cell to a hyperlink to start with, then copy just the value of the links to the already-formatted cells. That is, in your destination, create an active hyperlink cell and copy it to the range. Verify that the cells are now active as html links. Then, take your "text" hyperlinks, and paste the new links AS VALUES on top of those cells, and they should activate.