Exporting hyperlink from a table to Excel

[#1]

We have a table that contains a URL which is stored in our database.  Rather than show the full URL, there is a hyperlink (imaginitively named 'Link').  The formula looks like this:

='Link'&<url>'&PageTable.PageURL

This works like a treat in the application but when users export the table to Excel, they just see the text 'Link' and no URL. 

Is there a way to export this and keep the hyperlink, so it says 'Link' in Excel and the link still works?  Naturally we could just use the PageTable.PageURL field, but it looks messy in the report.

Thank you for your suggestions.

[#2]

There is not really an easy way to do it, but here is a work-around using the hyperlink expression in Excel.  Hope it helps.

[#3]

Thanks Rebeccad, that's very helpful.  I was hoping for a solution that wouldn't require a macro, but it looks as though that isn't an option.

[gmarshall]

Rebecca

That looks great but do you have an example where there are different URL links per row?

The example you have given has a fixed URL.

Thanks

[chandrasarva]

Hi,

did you get  a solution for getting URL's exported for multiple rows ?

I am looking for same solution here. For Example Below is the URL link getting generated for each row(with different req id) and when I need to export it has to export to excel with different corresponding URL links for each row.

'<URL>'&'https://server.opr.org/tlink/fire/lib/require/reqView.php?showReqSpecTitle=1&requirement_id='&Req_Id...''

[#6]

Hi,

One solution is to use an expression in the Excel Hyperlink Formula Format, e.g.:

='=HYPERLINK("https://[my website].com/' & [identifier] &'","' & [Friendly Excel Value] & '")'

Then you will find that within Excel it doesn't automatically recognise the formula by default. To force recognition the easiest way is to Replace (Ctrl+H) All equals '=' with equals '='.

The link should then work.

HYPERLINK function - Excel

worksheet function - What is the Excel hotkey to re-calculate all formula in sheet? - Super User

[#7]

Hi Rebecca,

i realise this is a VERY old post, but when I try and run that macro I get the module script open up in Qlikview and the Excel file which is created does not have a hyperlink in it.

What am i doing wrong?

[marcus_sommer]

Have you tried the suggestion below from AlexanderNimmo? I haven't tried them but they looked very interesting and would avoid any macro-stuff if they worked. The only thing what you needed by such an approach is to use a specialized object for your export - this means one for displaying within qlikview and one which will be exported.

Quite often it's a easier solution to use different objects (often placed in a hidden sheet) for printing and export then those which are used for displaying in qlikview to implement certain layout, format and other needs.

- Marcus

[#9]

I see what you mean Marcus.

If my link is defined by the field Ideas.Idea.Project_one_pager__c and the Friendly text i want displayed is 'Status'

How should the formula look? Also, what should the representation be set to? Text or Link?

This doesn't work

='=HYPERLINK("Ideas.Idea.Project_one_pager__c","' & Status & '")'

[#10]

This is the output

=HYPERLINK("Ideas.Idea.Project_one_pager__c","Status")

Obviously i need the field value displayed rather than the field name! But when I put square brackets round it in the expression, it invalidates it