Exporting HL to excel

[#1]

Hello Team,

I was trying to send my object data to excel. In fact, My straight table has 23 Dimensions and 44 Expressions. All are working fine. But in that I have Hyper links . But, When I was trying to do simple save as in excel it working for all left of Hyper links. Can some one assist me to do the same, Please?

Recently I reviewed few threads but I didn't find solution.

Note: I want to make with Hyper links in excel if user or employee hit that link it should go to there own URL's

Please assist me, I will be waiting for you

marcus_sommer jagan‌

[HirisH_V7]

Hi check out this,

using macros.

Load an Excel column with hyperlinks into QlikV... | Qlik Community

PFA,

HTH,

Hirish

[marcus_sommer]

AFAIK it's not possible with sending to excel because excel treats the link just as text. This meant you need to convert these strings within excel itself maybe with a macro which opens the excel after the export and does the converting-job (it might be also possible to outsource this to a xla-template whereby this would be work gloabally).

An alternatively would be to copy the table to excel by embedding the link into an excel-function otherwise it would be again only a string. It worked from the UI side but I see no reason why it shouldn't by using a macro. But you might need a second table for this approach which only purpose is the export. It's quite common to use such specialized objects for exporting/printing-tasks which are placed in a (hidden) extra-sheet.

- Marcus

[Anil_Babu_Samineni]

May be use this macro to achieve this

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("CH01")

      MyTableCount = MyTable.GetRowCount

      Set XLSheet = XLDoc.Worksheets(1)

      Mytable.CopyTableToClipboard True

      XLSheet.Range("B1:B2").Formula = "=Hyperlink(A1:A" & MyTableCount & ")"

End Sub

[Anil_Babu_Samineni]

I missed this to add

      XLSheet.Paste XLSheet.Range("A1") // This will allocate to A1 Cell value

Finally, This should be this

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("CH01")

      MyTableCount = MyTable.GetRowCount

      Set XLSheet = XLDoc.Worksheets(1)

      Mytable.CopyTableToClipboard True

      XLSheet.Paste XLSheet.Range("A1")

      XLSheet.Range("B1:B2").Formula = "=Hyperlink(A1:A" & MyTableCount & ")"

End Sub

[#6]

Do you mean those HL should work when we enter Manually. I love to go Macro if you share related one

[#7]

Some what, It is working as i expect. How can we control Hitting to go URL in Excel

[#8]

Done with this

Sub ExtractHL()

Dim HL As Hyperlink

For Each HL In ActiveSheet.Hyperlinks

HL.Range.Offset(0, 1).Value = HL.Address

Next

End Sub

from here, How to get from A1 Cell

[marcus_sommer · Accepted solution]

See here an example:

sub ExportWithHyperlink

Set xlApp = CreateObject("Excel.Application")

Set xlDoc = xlapp.Workbooks.Add

xlapp.Visible = true

Set xlSheet = xlDoc.Worksheets.Add

xlSheet.Name = "Test"

ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard true

xlSheet.Activate

xlSheet.Cells(1, 1).Select

xlSheet.Paste

xlSheet.Columns("B").ColumnWidth = 30

xlSheet.Cells(1, 1).Select

xlDoc.SaveAs "C:\ExportWithHyperlink.xls"

xlDoc.Close

end sub

- Marcus

[#10]

Thank you. It deserve it