Automatic hyperlinks in Excel?

Question

I am viewing data from an SSAS server to which users can connect from any tool they like (in practice, usually Excel 2007). We would like to provide URLs in some cells and we would like Excel to recognize them as hyperlinks and activate them and format them accordingly.

When we put the URL in the cell, it just displays as plain text. If you touch a cell and click it again, Excel activates it as a link.

The same thing happens if you send data directly from SQL or from a CSV file, there is nothing special in our use of SSAS.

Does anyone know a way to do these rendering as links automatically? We have no control over the creation of the spreadsheet so that macros are absent.

EDIT:

Just got a response from Meff. A good point, however, I forgot to mention that the place where we display the URLs is at the target of the drillthough action.

Answer 1

I do not believe that this will be possible without a macro.

The hyperlink in an Excel cell has nothing to do with the value of the cell. This is a separate object associated with a cell. A fully functional way to add a hyperlink to a cell is through the menu item Excel Insert → Hyperlink.

The fact that the hyperlink is added when the user enters a value, such as "http: // ...", "https: // ..." or "ftp: // ...", is just a GUI that has one same effect as Insert → Hyperlink. In your example, when the user “touches” the cell, they effectively enter the value “http: // ...” into the cell and the GUI shortcut for Insert → Hyperlink is called. However, the shortcut is NOT called when values ​​are entered into the cells programmatically, either through VBA or the built-in Excel functions (for example, data → Import external data).

For this reason, it is really difficult to make this arbitrary data import the corresponding values ​​in the form of hyperlinks without any macro. If you can convince your users to install a simple Excel add-in, you can provide a menu item that executes 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 can call this after importing / updating data. Obviously, this is far from ideal, because it relies on the user taking an extra step to render hyperlinks.

Answer 2

Take a look at the actions with URLs in SSAS, this allows them to right-click a cell and offer the opportunity to go to the link in the right-click menu:

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

Answer 3

without VBA .... I found that the easiest way to do this is to simply write a macro and apply a shortcut to the macro (e.g. ctrl -L). End users know that if they want to activate “links”, they press CTRL-L. My macro was written as ..... select the column, click the data, text into columns, next, next, next, complete), then click on the upper left cell a1 just for good measure. I had to save the file as an xlsm file (not sure why) in 2013

Answer 4

An easy way to manually update many links is to use a cell to link to a hyperlink, and then copy only the values ​​of links to already formatted cells. That is, at your destination, create an active hyperlink cell and copy it to the range. Make sure the cells are now active as html links. Then grab your “text” hyperlinks and insert new AS VALUES links on top of these cells and they should be activated.