Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Macros for exporting links to Excel

Hello Community.

I have been trying to implement the Macros in this thread to pass through links I have in my Straight table when "Sent to Excel".

https://community.qlik.com/t5/New-to-QlikView/Exporting-HL-to-excel/td-p/1257585

The table is going to excel fine but my links are not working.

I have received a "Microsoft cannot access the file 'C:\XXXX'. There are several possible reasons:..."

Any ideas of what I may be doing wrong?

Thanks. 

 

Labels (1)
1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

Re: Macros for exporting links to Excel

Hi Brett.

Thanks for the feedback. I figured out what it was (or maybe is a workaround).

I had to add another column in my field to point the macro to, then I conditionally formatted that filed item (turned white so invisible). 

Below is the macro I ended up with. My chart is 401, the reference field with the first part of the hyperlink is column DD and is being spit out in EE. 

Macro for exporting links to Excel
'Sub ExportToExcel()
'
' Set XLApp = CreateObject("Excel.Application")
'
' XLApp.Visible = True
'
' Set XLDoc = XLApp.Workbooks.Add
'
' XLDoc.Sheets(1).Name = "Export"
'
' Set XLSheet = XLDoc.Worksheets(1)
'
' Set MyTable = ActiveDocument.GetSheetObject("CH401")
'
' MyTableCount = MyTable.GetRowCount
'
' Set XLSheet = XLDoc.Worksheets(1)
'
' Mytable.CopyTableToClipboard True
'
' XLSheet.Paste XLSheet.Range("A1")
'
' XLSheet.Range("E2:E50").Formula = "=Hyperlink(D1:D" & MyTableCount & ")"
'
'End Sub

View solution in original post

2 Replies
Highlighted
Digital Support
Digital Support

Re: Macros for exporting links to Excel

Zoe, did you set 'System' Access in the Edit Module as the default Security level?  I am pretty sure this one is going to require that, if the security setting got set to 'Safe', that is likely not going to work.  Only thing of which I can think at the moment.

Also, is the link a directory path, or is it supposed to be an http URL?  If the prior, you will need to be sure to use UNC path, otherwise things are not going to work as far as I know.  Best I have for the moment.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Highlighted
Creator III
Creator III

Re: Macros for exporting links to Excel

Hi Brett.

Thanks for the feedback. I figured out what it was (or maybe is a workaround).

I had to add another column in my field to point the macro to, then I conditionally formatted that filed item (turned white so invisible). 

Below is the macro I ended up with. My chart is 401, the reference field with the first part of the hyperlink is column DD and is being spit out in EE. 

Macro for exporting links to Excel
'Sub ExportToExcel()
'
' Set XLApp = CreateObject("Excel.Application")
'
' XLApp.Visible = True
'
' Set XLDoc = XLApp.Workbooks.Add
'
' XLDoc.Sheets(1).Name = "Export"
'
' Set XLSheet = XLDoc.Worksheets(1)
'
' Set MyTable = ActiveDocument.GetSheetObject("CH401")
'
' MyTableCount = MyTable.GetRowCount
'
' Set XLSheet = XLDoc.Worksheets(1)
'
' Mytable.CopyTableToClipboard True
'
' XLSheet.Paste XLSheet.Range("A1")
'
' XLSheet.Range("E2:E50").Formula = "=Hyperlink(D1:D" & MyTableCount & ")"
'
'End Sub

View solution in original post