topic Re: Exporting hyperlink from a table to Excel in QlikView

Exporting hyperlink from a table to Excel

Tue, 30 Oct 2012 12:35:12 GMT

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.

Re: Exporting hyperlink from a table to Excel

Tue, 30 Oct 2012 16:24:01 GMT

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.

Re: Exporting hyperlink from a table to Excel

Wed, 31 Oct 2012 12:40:28 GMT

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.

Re: Exporting hyperlink from a table to Excel

gmarshall — Sat, 28 Jun 2014 11:04:28 GMT

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

Re: Exporting hyperlink from a table to Excel

chandrasarva — Tue, 16 Dec 2014 17:03:21 GMT

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&''

Re: Exporting hyperlink from a table to Excel

Thu, 08 Jan 2015 15:54:28 GMT

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

Re: Exporting hyperlink from a table to Excel

Mon, 26 Oct 2015 14:46:59 GMT

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?

Re: Exporting hyperlink from a table to Excel

marcus_sommer — Tue, 27 Oct 2015 07:13:01 GMT

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

Re: Exporting hyperlink from a table to Excel

Tue, 27 Oct 2015 08:05:58 GMT

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 & '")'

Re: Exporting hyperlink from a table to Excel

Tue, 27 Oct 2015 08:11:36 GMT

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

Re: Exporting hyperlink from a table to Excel

Tue, 27 Oct 2015 08:33:58 GMT

Sorted it!

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