Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
hi
Hi.....you can do this by Macros....
Write a macro for exporting teh URL
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
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
Thank you DV, I will give it a shot.
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?
I got it - the name of the macro I was calling was not the same as the name in the module. Works great!