topic Re: How can I export title Caption in Excel? in QlikView

How can I export title Caption in Excel?

Thu, 27 Aug 2015 07:53:36 GMT

Hi,

i need to insert Chart/Report Caption Title when I extract to excel.

I see that I can use a macro for that, but when I test the code I'm getting error like

"ActiveX component can't create object: 'Excel.Application' "

I Use this code:

Sub ExcelExpwCaption

'Set the path where the excel will be saved

filePath = "C:\"

'Create the Excel spreadsheet

Set excelFile = CreateObject("Excel.Application")

excelFile.Visible = true

'Create the WorkBook

Set WorkBook = excelFile.WorkBooks.Add

'Create the Sheet

Set Sheet = WorkBook.WorkSheets(1)

'Get the chart we want to export

Set tableToExport = ActiveDocument.GetSheetObject("LB02")

Set chartProperties = tableToExport.GetProperties

tableToExport.CopyTableToClipboard true

'Get the caption

chartCaption = tableToExport.GetCaption.Name.v

'MsgBox chartCaption

'Set the first cell with the caption

Sheet.Range("A1") = chartCaption

'Paste the rest of the chart

Sheet.Paste Sheet.Range("A2")

excelFile.Visible = true

'Save the file and quit excel

WorkBook.SaveAs filePath

WorkBook.Close

excelFile.Quit

'Cleanup

Set WorkBook = nothing

Set excelFile = nothing

End Sub

Is there another way to see title in exported file?

Thanks a lot

regards

Francesca

Re: How can I export title Caption in Excel?

Thu, 27 Aug 2015 08:23:38 GMT

Hello. Your script is written is true. Problem in configuring security levels for macros. In the macro editor, adjust the desired level or press Ctrl-Shift-M.

Re: How can I export title Caption in Excel?

Thu, 27 Aug 2015 08:53:44 GMT

This Code is only for one Report.

I want to Export Caption Title for every report in my dashboards.

How can I do?

Re: How can I export title Caption in Excel?

Thu, 27 Aug 2015 09:04:36 GMT

This is not a trivial task. For each report, write a separate piece of code, or use a loop to retrieve all names and ID reports. It's not easy. In this I will not be able to help you.

Re: How can I export title Caption in Excel?

jonathandienst — Thu, 27 Aug 2015 09:20:09 GMT

If you are running this on a server, then it is possible (even likely) that Excel is not installed on the server. that call requires that Excel is installed and accessible to the user (or service account) running the script.

Re: How can I export title Caption in Excel?

Thu, 27 Aug 2015 13:59:17 GMT

Thanks a lot!

How can i see report name when i export to excel?

Re: How can I export title Caption in Excel?

Thu, 27 Aug 2015 14:05:15 GMT

What do you mean? For example "LB02".

Re: How can I export title Caption in Excel?

Thu, 27 Aug 2015 14:12:35 GMT

LB02 is che object ID. I need to see Title of the object when export to excel

Re: How can I export title Caption in Excel?

Thu, 27 Aug 2015 14:24:40 GMT

In this string from you macros

chartCaption = tableToExport.GetCaption.Name.v ----> you get title

Sheet.Range("A1") = chartCaption -----> put title into excel file