topic Re: URL active afer export to Excel in QlikView

URL active afer export to Excel

Anonymous — Fri, 20 Nov 2020 18:02:30 GMT

Hi, I have links in my application that take me to a url from within the application, however, the links do not work when the table is exported Excel. Is that possible to do?

URL active afer export to Excel

IAMDV — Tue, 06 Dec 2011 17:44:59 GMT

Hi,

I am not sure if you can do this without using Macro/VB Script. I can try that route if you are okay? Also, I am very keen to see what fellow members think about this issue...

Cheers - DV

URL active afer export to Excel

Wed, 07 Dec 2011 06:33:37 GMT

URL active afer export to Excel

kamalqlik — Wed, 07 Dec 2011 06:40:14 GMT

Hi.....you can do this by Macros....

Write a macro for exporting teh URL

Re: URL active afer export to Excel

IAMDV — Wed, 07 Dec 2011 12:02:27 GMT

Hi,

Here you go... I have written VB Script to export as HyperLink. You need to modify the code based on your requirements, I have provided comments for each line for your understanding.

Here is the code :

Sub ExportToExcel()
      Set XLApp = CreateObject("Excel.Application") 'Creating excel instance in VB Script
      XLApp.Visible = True 'Settting the value to False, so this happens in background
      Set XLDoc = XLApp.Workbooks.Add 'Creating new excel workbook

      XLDoc.Sheets(1).Name = "Export" 'Adding the sheet name as Export
      Set XLSheet = XLDoc.Worksheets(1) 'Setting the XLSheet
      Set MyTable = ActiveDocument.GetSheetObject("LB01") 'Setting MyTable variable to current QlikView Object
      MyTableCount = MyTable.GetRowCount

      Set XLSheet = XLDoc.Worksheets(1) 'Select sheet where data should be pasted
      Mytable.CopyTableToClipboard True 'Copy data to Clipboard
      XLSheet.Paste XLSheet.Range("A1") 'Paste data starting at a1
      XLSheet.Range("B1:B2").Formula = "=Hyperlink(A1:A" & MyTableCount & ")" 'This is Excel formula adds a new column and changes the format
End Sub

I hope this helps!

Cheers - DV

Re: URL active afer export to Excel

IAMDV — Wed, 07 Dec 2011 12:13:26 GMT

I forgot to mention that you need to "Allow System Access" security settings under Edit Module window. Because we are communicating with Excel and making some changes.

Good luck!

Cheers - DV

Re: URL active afer export to Excel

Anonymous — Wed, 07 Dec 2011 12:56:19 GMT

Thank you DV, I will give it a shot.

Re: URL active afer export to Excel

Anonymous — Wed, 28 Nov 2012 14:20:41 GMT

That is very cool and I have it working with one exception - Just clcking on my macro button to export has no effect, however, if I go into edit the macro and use "test" it works fine. Any ideas?

Re: URL active afer export to Excel

IAMDV — Wed, 28 Nov 2012 15:14:23 GMT

Hi,

Please share your QVW file and I'll look into it.

Thanks,

www.Qlik.com

Re: URL active afer export to Excel

Anonymous — Wed, 28 Nov 2012 15:21:48 GMT

I got it - the name of the macro I was calling was not the same as the name in the module. Works great!

Re: URL active afer export to Excel

IAMDV — Wed, 28 Nov 2012 15:23:51 GMT

That's great.

Cheers,

www.Qlik.com

Re: URL active afer export to Excel

Tue, 19 Aug 2014 14:57:24 GMT

Hi DV,

I have tried your code, and I find that after it executes, I am returned to the "Edit Module" page, and not to the actual QV document.

I use macros a lot, and this never occurred before.

Do I need to add some commands to EXIT from the module?

Re: URL active afer export to Excel

IAMDV — Wed, 20 Aug 2014 09:16:44 GMT

Kevin - Please can you post the QVW file?

Thanks,