topic Exporting HL to excel in QlikView

Exporting HL to excel

Wed, 25 Nov 2020 16:16:04 GMT

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‌

Re: Exporting HL to excel

HirisH_V7 — Thu, 09 Mar 2017 09:26:39 GMT

Hi check out this,

using macros.

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

PFA,

HTH,

Hirish

Re: Exporting HL to excel

marcus_sommer — Thu, 09 Mar 2017 09:35:49 GMT

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

Re: Exporting HL to excel

Anil_Babu_Samineni — Thu, 09 Mar 2017 17:07:43 GMT

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

Re: Exporting HL to excel

Anil_Babu_Samineni — Thu, 09 Mar 2017 17:09:32 GMT

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

Re: Exporting HL to excel

Fri, 10 Mar 2017 05:06:19 GMT

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

Re: Exporting HL to excel

Fri, 10 Mar 2017 05:06:57 GMT

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

Re: Exporting HL to excel

Fri, 10 Mar 2017 05:08:11 GMT

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

Re: Exporting HL to excel

marcus_sommer — Fri, 10 Mar 2017 09:01:29 GMT

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

Re: Exporting HL to excel

Mon, 20 Mar 2017 05:27:15 GMT

Thank you. It deserve it