Macros for exporting links to Excel

[ZoeM]

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. 

[Brett_Bleess]

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

[ZoeM · Accepted solution]

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