Solved: URL active afer export to Excel - Qlik Community

Anonymous · ‎2011-12-06

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?

IAMDV · ‎2011-12-07

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

View solution in original post

IAMDV · ‎2011-12-06

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

Report Inappropriate Content · ‎2011-12-07

hi

kamalqlik · ‎2011-12-07

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

Write a macro for exporting teh URL

IAMDV · ‎2011-12-07

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

IAMDV · ‎2011-12-07

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

Anonymous · ‎2011-12-07

Thank you DV, I will give it a shot.

Anonymous · ‎2012-11-28

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?

IAMDV · ‎2012-11-28

Hi,

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

Thanks,

DV

www.Qlik.com

Anonymous · ‎2012-11-28

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